| How to reduce the execution timing of SQL Query [message #309594] |
Fri, 28 March 2008 01:42  |
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
|
Member |
|
|
Hi,
Can any one help me out , I have a SQL query & i want to reduce the Execution timing og this:
select file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;
I have indexing on column
a) direction
b) status
c) file_no
and the execution timing of this is 3.10sec.
Thanx in advance.
|
|
|
|
|
|
|
|
| Re: How to reduce the execution timing of SQL Query [message #309623 is a reply to message #309599] |
Fri, 28 March 2008 03:41  |
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
|
Member |
|
|
Hi,
Thanks for replying.
a) Table structure
SQL> desc file_table ;
Name Null? Type
----------------------------------------- -------- ----------------------
FILE_NO NOT NULL VARCHAR2(12)
DIRECTION VARCHAR2(2)
ORIGIN_CODE VARCHAR2(4)
ORIGIN_NAME VARCHAR2(20)
LOAD_CODE VARCHAR2(4)
LOAD_NAME VARCHAR2(20)
DISCH_CODE VARCHAR2(4)
DISCH_NAME VARCHAR2(20)
DEST_CODE VARCHAR2(4)
DEST_NAME VARCHAR2(20)
ETS_DATE DATE
ETA_DATE DATE
CONTAINER VARCHAR2(11)
CONT_SIZE VARCHAR2(3)
LINE_BOOKING_NO VARCHAR2(16)
STATUS VARCHAR2(1)
b) This query is used in LOV of file_no.
c)Total no of rows selected is : 10017 rows selected.
d)Data of these column:
SQL> select file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;
FILE_NO ETS_DATE LOAD DISC CONTAINER
------------ --------- ---- ---- -----------
703063170 21-MAR-07 NYC LAM NYKU2397112
703044237 28-MAR-07 NYC FLX GSTU9563649
702903129 14-FEB-07 NYC CPH 1
702063WHS 01-FEB-07 NYC NYC IN/OUT
701834023 13-AUG-07 OAK ROT NYKU2455230
70103098 15-SEP-07 PEV RIH 709103098
610063PDM 01-OCT-06 NYC NYC OCT/06 PDM
609023ERR 01-SEP-07 ATL ATL X
608904001 01-AUG-06 NYC ALX TBA
608104064 28-AUG-06 HOU PTE MAEU7804775
1606220 11-SEP-07 HOU THA EMCU9432640
FILE_NO ETS_DATE LOAD DISC CONTAINER
------------ --------- ---- ---- -----------
1605940 29-AUG-07 HOU HAM 1
1600133 26-JUL-07 OAK DAL NYK5954504
1589700 03-JUL-07 LAX LAM TRLU6504562
1202827 22-DEC-07 ABY ABO SXD2212
071084003 01-OCT-07 HOU FPT MSCU2986064
070644297 02-JUL-07 NFK BHV TGHU8343240
036VF6 036VF6 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to reduce the execution timing of SQL Query [message #309730 is a reply to message #309695] |
Fri, 28 March 2008 10:56 |
Tafer
Messages: 64
Registered: July 2005
Location: Here!
|
Member |
|
|
If by LOV you mean an Oracle Form's List of Values, please consider this:
1. If the query is returning 10k lines, check the properties of both the LOV and the associated Record Group, and reduce the amount of records fetched to something "acceptable". (< 100 for example)
2. You may want to set off the LOV property "Automatic Refresh". (Read the documentation related to this).
3. Please post the explain plan of your query.
I hope this helps.
[Updated on: Fri, 28 March 2008 10:59] Report message to a moderator |
|
|
|
| Re: How to reduce the execution timing of SQL Query [message #312280 is a reply to message #309730] |
Tue, 08 April 2008 07:07 |
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
|
Member |
|
|
HI,
very sorry for giving answer very late.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=69 Card=15
Bytes=840)
1 0 COUNT (STOPKEY)
2 1 FILTER
3 2 MAT_VIEW ACCESS (FULL) OF 'MV_FILE_TABLE1' (MAT_VIEW)
(Cost=69 Card=101 Bytes=5656)
|
|
|
|
| Re: How to reduce the execution timing of SQL Query [message #312871 is a reply to message #309594] |
Thu, 10 April 2008 00:26 |
singh.neerajin
Messages: 26
Registered: April 2008
Location: noida
|
Junior Member |
 
|
|
select file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;
In this query...
1 Supppose If the column 'status' contains 10 unique records out of total 100 records then create a BIT MAP INDEX on this column. If doesnt then create a normal index.
2. same If the column 'direction ' contains 10 unique records out of total 100 records then create a BIT MAP INDEX on this column. If doesnt then create a normal index.
If your using a bit map index then use query
BIT_status is bitmap index on status column and BIT_direction is bitmap index on direction column on table file_name
select /*+INDEX_COMBINE(file_table BIT_status BIT_direction )*/file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;
if your using a composite index suppose 'ind_dir_stat' for 'direction and status column on table file_name then use the query
select /*+ INDEX_FFS ( file_table ind_dir_stat ) */file_no, ets_date, load_code, disch_code, container
from file_table where status = 'O'
and direction = 'EO' order by file_no desc;
I think it would work out faster...
[Updated on: Thu, 10 April 2008 00:30] Report message to a moderator |
|
|
|
| Re: How to reduce the execution timing of SQL Query [message #312905 is a reply to message #312871] |
Thu, 10 April 2008 01:39 |
AnamikaChaudhary
Messages: 33
Registered: December 2007
Location: Mumbai
|
Member |
|
|
Hi,
When i issue a command to create a bit map index then it shows me an error:
SQL> create BITMAP index
2 emp_bit_idx
3 on emp(ename);
create BITMAP index
*
ERROR at line 1:
ORA-00439: feature not enabled: Bit-mapped indexes
after that i issued a command:
SQL> select parameter, value from v$option;
PARAMETER VALUE
---------------------------------------------------------------- -----------------
Partitioning FALSE
Objects TRUE
Real Application Clusters FALSE
Advanced replication FALSE
Bit-mapped indexes FALSE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing TRUE
Incremental backup and recovery TRUE
Instead-of triggers TRUE
Parallel backup and recovery FALSE
SQL> select * from v$version;
BANNER
----------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - Production
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production
Is BIT MAP INDEXING IS for the above mentioned version of oracle ?
Can i set the value of parameter(Bit map indexing) to TRUE ?
Please help me for this
Thanks & Regards
|
|
|
|
|
|
| Re: How to reduce the execution timing of SQL Query [message #312944 is a reply to message #312941] |
Thu, 10 April 2008 03:18 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
| singh.neerajin wrote on Thu, 10 April 2008 18:05 |
you should use BIT MAP index on those column only which have distinct values 6-10 out of 1000 total records on average.
|
Is that it? I would have thought there were some other considerations that would be worth mentioning before the OP goes and crashes an OLTP application with bitmap indexes.
In almost all cases, a single b-tree index will out-perform bitmap indexes. Bitmap indexes should only be used in DSS systems that permit ad-hoc unpredictable access paths.
If you are selecting <10% of the table, you may get a performance improvement from a single index on (status, direction). If you are selecting >10%, then an index probably will not help.
If you need to improve performance further, you may add file_no as a third column in this index. It is possible for Oracle to use the index to satisfy the sort, although you will have difficulty getting this to work for ORDER BY DESC. You may need to create a Descending Key index; and you may need to add hints to the query. It may take some effort - don't bother if the performance of the initial suggestion is adequate.
If you are selecting a lot of rows (1-10%) then you may want to add the other selected columns to the index (ets_date, load_code, disch_code, container). This will take up a LOT more space and make the index less effective for other queries, but it will avoid a table lookup for THIS query. Only do this if you are DESPERATE for more speed.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
