Indexes

this group of ddl sql statements allows database administrators to manage indexes database administrators can create and drop indexes you can view information about indexes using the sys indexes system catalog table for information on other database components, see the pages on docid\ uy5crlqbr0ymvtqzwna6x , docid\ m9zbpvfqpm96jidxyhqvi , docid\ yhp4b1irv haf8f3df ww , and docid\ g tzn2zxb4zp8w7za8a1k create index create index creates a new secondary index indexes help optimize database queries when created on columns that are frequently referenced for more information on how {{ocient}} indexes operate, see docid\ a5odjf1wlmeacsfrr8k4m creating an index does not trigger re indexing of existing segments only segments generated after the create index is issued contain the new index indexes can be created on columns containing various different data types as long as the requirements are met please note that depending on the data type, the system can assign different index types by default if you decline to specify which index type to use the name must be distinct from the name of any existing index on the table you can apply indexes regardless of whether they have gdc compression syntax create index \[ if not exists ] index name on table (column name) \[ using \<index type> ] \<index type> = inverted | hash | ngram \[ (n value) ] | spatial | zone map true 193,105,282 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type index types ( \<index type> ) ocient supports four index types alongside the clustering index inverted , hash , ngram , and spatial an index notionally stores a mapping of a column value to the rows that contain that value, and the index type differentiates the format in which the column values are stored and accessed unless an index type is explicitly specified with a using clause, the data type of a column determines a default index type that the system creates for information on index type defaults, see docid\ a5odjf1wlmeacsfrr8k4m for container data types (e g , arrays and tuples), the index stores the internal elements of the container, and is used on predicates that target the internal values however, a mapping of null column values is generally stored for both scalar and container data types, so the index can always be used for column is null predicates true 193,193,194 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type 1 1 unhandled content type 1 1 unhandled content type 1 1 unhandled content type for further description and examples of the index types, see docid\ a5odjf1wlmeacsfrr8k4m examples this example creates an index named new idx on the address column of the table because address is a varchar column, this index defaults to the hash index type create index new idx on employees (address); this example creates an index of type ngram on the address column as the ngram has no specified n value , it defaults to indexing substrings of three characters long create index ngram address idx on employees (address) using ngram; this example creates an index on a component of the tuple col column as this column is of data type int , the index defaults to using the inverted type create index tuple index on employees (tuple col\[1]); this example creates an index on a component of the point col column as this column is of data type point , the index defaults to using the spatial type create index spatial index on employees (point col) drop index drop index drops a secondary index on a table after an index is dropped, new segments that are generated do not contain the new index however, no existing segments will be altered this means that until a segment is rebuilt, you can still use the removed index internally, and the system does not reclaim the storage space the removed index occupied syntax drop index \[ if exists ] index name on table name true 193,193,194 unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type unhandled content type example this example drops the index named new idx on the employees table drop index new idx on employees; related links docid nwuyof4 i 7wgmmmbf4j docid\ nw9vavkey2v75moxm muo docid\ jfltms91v2bfledppksy8 https //docs ocient com/system catalog