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 parameter type description index name string an identifier for the index to create the name must be distinct from the name of any existing index on the table table string the name of the table for the index column name string the name of the column for the index identical indexes on the same column are not allowed a column can only have multiple indexes if they are of different types or parameters n value integer optional when used with an ngram index, this numeric value specifies the character length of the substrings to be indexed if unspecified, this value defaults to 3 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 index type primary data types primary usage description inverted fixed length numeric columns stores whole column value internally, meaning its storage size is approximately the same as the width of the data type supports lookups using strict equality or range comparisons for details, see the docid\ a5odjf1wlmeacsfrr8k4m section hash variable length character columns stores a hash of the indexed column value rather than the full value primarily used for exact comparisons for details, see the docid\ a5odjf1wlmeacsfrr8k4m section ngram variable length character columns stores substrings equal in size to its n value storage requirements can greatly vary depending on column data size, width and cardinality supports exact string comparison and filters including like , not like , similar to and not similar to for details, see the docid\ a5odjf1wlmeacsfrr8k4m section spatial geospatial columns ( point , linestring , polygon ) groups geographic objects for bounding box filtering for details, see the docid\ a5odjf1wlmeacsfrr8k4m section zone map fixed length columns (int, bigint, smallint, float, double, binary, decimal, hash, char, ip / ipv4, time, timestamp, date, uuid) supports range and exact matches on a whole segment level the system stores the minimum and maximum values for the column per segment 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 parameter type description index name string an identifier for the index to drop table name string the name of the table with the index to drop 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