Showing posts with label Indexes. Show all posts
Showing posts with label Indexes. Show all posts
Friday, March 11, 2022

How to create a partial index on a table in PostgreSQL

›
I was asked by one of my customers to advise on how to create an index for a new column called "sk_type", of datatype varchar, wi...
Monday, January 31, 2022

Simple SQL to list

›
The following SQL lists the indexes defined on a table, along with the columns and their positioning: SELECT I.INDEX_NAME,I.INDEX_TYPE,I.N...
Wednesday, June 23, 2021

How to list index sizes

›
For non-partitioned global indexes in a specific schema: select i.index_name, s.tablespace_name,round(sum(bytes)/1024/1024/1024) "GB...
Wednesday, July 29, 2020

How to rebuild an index partition

›
To limit a rebuild to a specific index partition only, use the following syntax: ALTER INDEX SH.SALES_IDX4 REBUILD PARTITION SYS_P51490 ...

How to list index partitions and their sizes

›
To list index partitions for index SALES_IDX1: SELECT IDXPART.INDEX_OWNER, IDXPART.INDEX_NAME, IDXPART.PARTITION_NAME, IDXPART.TABLESPACE...
Wednesday, March 25, 2020

How to list index subpartitions and their sizes

›
SELECT IDXSUBPART.INDEX_OWNER, IDXSUBPART.INDEX_NAME, IDXSUBPART.PARTITION_NAME, IDXSUBPART.SUBPARTITION_NAME,IDXSUBPART.TABLESPACE_NAME, ...
Tuesday, March 24, 2020

How do you move an index organized table?

›
The following index needs to be moved from tablespace DATA1 to tablespace DATA2: select unique segment_name,segment_type,tablespace_name...
Wednesday, August 21, 2019

How to create a compressed index using advanced index compression

›
Applicable for Oracle 12.1 and onwards. Basic syntax: CREATE INDEX MYIDX1 ON MYTABLE(COL1, COL2) COMPRESS ADVANCED LOW; The above st...
Monday, August 5, 2019

How to find indexes on a table in postgres

›
If you are logged in as a user whose search_path includes a schema the user has USAGE privilege on, use: SELECT * FROM pg_indexes WHERE...
Wednesday, June 12, 2019

How to use dbms_stats.report_col_usage

›
set long 10000 select dbms_stats.report_col_usage('SALES', 'ORDERS') from dual; exit Output could look something like thi...
Tuesday, April 9, 2019

How to create a unique constraint with an existing index (the USING-clause)

›
Create the index: CREATE UNIQUE INDEX MYTABLE_IDX1 ON MYTABLE (ID,CATEGORY) LOCAL TABLESPACE USERS ONLINE; Create a unique constraint: ...
Thursday, February 14, 2019

Syntax for using the dbms_stats.gather_index_stats procedure

›
How to analyze an index using dbms_stats: BEGIN SYS.DBMS_STATS.GATHER_INDEX_STATS ( OwnName => 'SCOTT' ,...
Thursday, January 10, 2019

What are Index-organized tables and how are they created?

›
An example of creating an IOT table from my own experience: CREATE TABLE DATE_TO_SEQNUM ( DATO DATE, SEQNUM NUMBER(19) NOT NULL, ...
Thursday, December 20, 2018

How to generate a script to rebuild unusable indexes

›
set trimspool on set lines 200 set pages 0 set heading off set verify off set feedback off set echo off spool re...
Tuesday, April 24, 2018

Partitioned index properties

›
The following query will reveal information about the indexes on a partitioned table. Very useful when you want to investigate if a part...
Tuesday, April 17, 2018

Syntax for index hint

›
Use /*+ INDEX(table_name index_name) */ For example: set autot trace exp stat select /*+ INDEX(MYTABLE MY_IDX1) */ min(entry_num) from s...
Tuesday, August 1, 2017

How to rebuild an index subpartition

›
To limit a rebuild to a specific index subpartition only, use the following syntax: ALTER INDEX SCOTT.SALES_IDX3 REBUILD SUBPARTITION ...
Thursday, July 13, 2017

Possible solution to ORA-01450: maximum key length (3800) exceeded when rebuilding an index

›
As a part of disabling TDE in a test database, I was moving indexes out of the TDE encrypted tablespace to another, similarly created tables...
Thursday, February 16, 2017

Create a globally partitioned hash index

›
Partitioned table layout: CREATE TABLE SCOTT.ARCHIVED_NOTES ( NOTE_ID NUMBER(10) NOT NULL, PPT_ID ...
2 comments:

Create a globally partitioned range index

›
CREATE TABLE SCOTT.ARCHIVED_NOTES ( NOTE_ID NUMBER(10) NOT NULL, PPT_ID NUMBER(10), R...
1 comment: