Feeds:
Posts
Comments

Posts Tagged ‘Data Definition Language’

* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
* To improve performance on joins of multiple tables, index columns used for joins.

* Small tables do not require indexes.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

* Values are relatively unique in the column.
* There is a wide range of values (good for regular indexes).
* There is a small range of values (good for bitmap indexes).
* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

* There are many nulls in the column and you do not search on the not null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Other Considerations:

1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

3. Drop Index that are no longer required.

4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.

Expert are always welcome for their valuable comment or suggestion for the above post.

Related Post:

https://samadhandba.wordpress.com/2011/01/04/oversize-of-datatype-varchar2-causes-performance-issue/

https://samadhandba.wordpress.com/2011/02/16/what-and-when-index-scans-is-used/

https://samadhandba.wordpress.com/2011/02/16/index-skip-full-fast-full-index-index-joins-bitmap-indexes-scan/

https://samadhandba.wordpress.com/2011/02/14/sample-table-scans-in-oracle/

https://samadhandba.wordpress.com/2011/02/14/when-you-would-make-index-and-when-not/

https://samadhandba.wordpress.com/2011/02/14/optimize-data-access-path-in-oracle-2/

https://samadhandba.wordpress.com/2011/02/13/troubleshoot-unusable-index-in-oracle/

https://samadhandba.wordpress.com/2011/02/13/the-possible-causes-for-excessive-undo-generation-2/

https://samadhandba.wordpress.com/2011/02/13/three-basic-steps-of-sql-tuning/

https://samadhandba.wordpress.com/2011/02/13/goals-for-tuning-2/

https://samadhandba.wordpress.com/2011/02/04/ora-12054-cannot-set-the-on-commit-refresh-attribute-for-the-materialized-view/

Read Full Post »

One day I was at client side where need to increase the speed of insert statement.I sneed to insert over 500K record to database as quickly as possible. We were inserting at a rate of 10 records/sec. Well, I was thinking the following approaches to gain speed-

1. Use a large blocksize – By defining large (i.e. 16k or 32k) blocksizes for the target tables, we can reduce I/O because more rows fit onto a block before a “block full” condition (as set by PCTFREE) unlinks the block from the freelist.

>DROP TABLESPACE web_key_tbs INCLUDING CONTENTS AND DATAFILES;
>CREATE TABLESPACE web_key_tbs DATAFILE ‘/mnt/extra/test1.dbf’ SIZE 1024M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED blocksize 16K;

2. Increase the size of UNDO tablespace –

>CREATE UNDO TABLESPACE UNDOTBS DATAFILE ‘/mnt/extra/test_undo1.dbf’ SIZE 200M BLOCKSIZE 16K;
>alter system set undo_tablespace=’UNDOTBS‘ scope=both;
>DROP TABLESPACE UNDOTBSP1 INCLUDING CONTENTS AND DATAFILES;

3. APPEND into tables – By using the APPEND hint, it ensures that Oracle always grabs fresh data blocks by raising the high-water-mark for the table.

>insert /*+ append */ into customer values (’hello’,’;there’);

4. Table into NOLOGGING mode – Putting the table into NOLOGGING mode, which will allow Oracle to avoid almost all redo logging.

>SELECT logging FROM user_tables WHERE table_name = ‘LOGIN’;
>ALTER TABLE login NOLOGGING;

Again, to enable logging –
>ALTER TABLE login LOGGING;

5. Disable/drop indexes – It’s far faster to rebuild indexes after the data load, all at-once. Also indexes will rebuild cleaner, and with less I/O if they reside in a tablespace with a large block size.(As I dissused in my previous post of Database design approach )

6. Parallelize the load – We can invoke parallel DML (i.e. using the PARALLEL and APPEND hint) to have multiple inserts into the same table. For this INSERT optimization, make sure to define multiple freelists and use the SQL “APPEND” option.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »

* Create an index if you frequently want to retrieve less than 15% of the rows in a large table.
* To improve performance on joins of multiple tables, index columns used for joins.

* Small tables do not require indexes.

Some columns are strong candidates for indexing. Columns with one or more of the following characteristics are candidates for indexing:

* Values are relatively unique in the column.
* There is a wide range of values (good for regular indexes).
* There is a small range of values (good for bitmap indexes).
* The column contains many nulls, but queries often select all rows having a value. In this case, use the following phrase:

WHERE COL_X > -9.99 * power(10,125)

Using the preceding phrase is preferable to:

WHERE COL_X IS NOT NULL

This is because the first uses an index on COL_X (assuming that COL_X is a numeric column).

Columns with the following characteristics are less suitable for indexing:

* There are many nulls in the column and you do not search on the not null values.

The size of a single index entry cannot exceed roughly one-half (minus some overhead) of the available space in the data block.

Other Considerations:

1. The order of columns in the CREATE INDEX statement can affect query performance. In general, specify the most frequently used columns first.If you create a single index across columns to speed up queries that access, for example, col1, col2, and col3; then queries that access just col1, or that access just col1 and col2, are also speeded up. But a query that accessed just col2, just col3, or just col2 and col3 does not use the index.

2. There is a trade-off between the speed of retrieving data from a table and the speed of updating the table. For example, if a table is primarily read-only, having more indexes can be useful; but if a table is heavily updated, having fewer indexes could be preferable.

3. Drop Index that are no longer required.

4. Using different tablespaces (on different disks) for a table and its index produces better performance than storing the table and index in the same tablespace. Disk contention is reduced.

Expert are always welcome for their valuable comment or suggestion for the above post.

Read Full Post »