Multiple index queries, max number of indexes
Ramesh Golkonda, March 20, 2002 - 10:23 am UTC
Tom,
My question is realted to multiple single column indexes. We have got two tables and indexes on those tables are as follows :
Table T1
Index REQST_N4
Index REQST_N4
Table T2 :
Index : EVENT_I01
Data on the index REQST_N4 IS SKEWED IN THE FOLLOWING MANNER :
TYPE = 100 (contains nearly 150,000 rows)
TYPE = 102 (contains nearly 200,000 rows)
TYPE = 103 (contains nearly 95,000 rows)
TYPE = 104 (contains nearly 170,000 rows)
Data on REQST_N5 IS SKEWED IN THE FOLLOWING MANNER :
ID = Each ID contains 15 rows
T1.ID and T1.TYPE ARE BIND VARIABLES (i.e we input these values each time we run the query)
Select event_id
from T1, T2
where t1.reqtype = 100 -- (index on t1.reqtype is REQST_N4)
and t1.id = 5 -- (index on t1.reqtype is REQST_N5)
and t2.reqid = t1.reqid
and t2.typeid = 'AE'
Order by t2.edate
Produces following plan :
ID PARENT_ID POSITION Query Plan
-- --------- --------- ------------------------------------------
0 SELECT STATEMENT Cost =
1 0 1 SORT ORDER BY
2 1 1 NESTED LOOPS
3 2 1 TABLE ACCESS BY INDEX ROWID T1
4 3 1 AND-EQUAL
5 4 1 INDEX RANGE SCAN REQST_N4
6 4 2 INDEX RANGE SCAN REQST_N5
7 2 2 TABLE ACCESS BY INDEX ROWID T2
8 7 1 INDEX RANGE SCAN EVENT_I01
Questions :
1) Which index should I be hitting REQST_N4 OR REQST_N5 ?
2) Does the order matters ?
Thanks
RameshG
March 20, 2002 - 2:25 pm UTC
Well, I don't see a REQST_N5 but....
REQST_N4 should be DROPPED. It is a b*tree index on 615,000 rows that have 4 discrete values. It is useless.
You might have a concatenated index on t1(id,reqtype) but a b*tree index on t1(reqtype) is worthless.
Mutilple indexes
Ramesh, March 21, 2002 - 5:01 am UTC
Apologies for the mistake.
Select event_id
from T1, T2
where t1.reqtype = 100 -- (index on t1.reqtype is REQST_N4)
and t1.id = 5 -- (index on t1.id is REQST_N5)
and t2.reqid = t1.reqid
and t2.typeid = 'AE'
Order by t2.edate
----
That's what I had thought it is useless. We are are using RULE BASED optimizer and if we had used cost-based the index REQST_N4 would not have appeared. Funny thing is when I turn this index off we are getting the results in the same time as it was with this index, this won't happen unless oracle uses bit different algorithm than what you mentioned. I need to give more trouble, let's assume it is a useless index why am I getting results instantly ?
Thanks
RameshG
March 21, 2002 - 4:08 pm UTC
why wouldn't it happen? ANYTHING can happen. use sql_trace+tkprof to analyze the differences in more detail. without something like that, I cannot really comment. You give me NOTHING to compare to.
Multiple indexes
Kiran, April 05, 2002 - 10:25 am UTC
Tom,
I am struggling with the multiple indexes concept using Index Join.I have two b-tree indexes on the table and I want to fetch the data only by joining these two indexes with out scanning the table.These two indexes have all the columns in my query.I tried using the hint /*+ INDEX_JOIN(TAB1 IND1 IND2) */ but It didn't work.Could you explain how to use the Index Join on the B-tree index.What are the INIT.ora parameters we have to set to enable this feature.
Thanks a lot,
Kiran
April 05, 2002 - 11:56 am UTC
Examples -- one always needs an example of what one has tried in order to explain why something did or didn't happen.
Could be a multitude of reasons. Most probable include:
o you tested without stats
o you tested on too small a set of data
o you have the indexes on nullable columns and included no predicates
o full scanning was BETTER then index joining and we ignored as (they are hints, not directives!)
ops$tkyte@ORA817DEV.US.ORACLE.COM> @desc big_table
Datatypes for Table big_table
Data Data
Column Name Type Length Nullable
------------------------------ -------------------- ----------- --------
OWNER VARCHAR2 30 not null
OBJECT_NAME VARCHAR2 30 not null
SUBOBJECT_NAME VARCHAR2 30 null
OBJECT_ID NUMBER not null
DATA_OBJECT_ID NUMBER null
OBJECT_TYPE VARCHAR2 18 null
CREATED DATE 7 not null
LAST_DDL_TIME DATE 7 not null
TIMESTAMP VARCHAR2 19 null
STATUS VARCHAR2 7 null
TEMPORARY VARCHAR2 1 null
GENERATED VARCHAR2 1 null
SECONDARY VARCHAR2 1 null
Indexes on big_table
Index Is
Name Unique COLUMNS
------------------------------ ------ --------------------------------
BIG_TABLE_IDX1 No OBJECT_NAME
BIG_TABLE_IDX2 No OBJECT_ID
<b>Now, I have no problems with this 1,000,000 row table getting an index join going:</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select /*+ index_join( big_table big_table_idx1 big_table_idx2 ) */ object_name, object_id
2 from big_table
3 where object_name like 'ABCDEF%'
4 and object_id between 1000 and 1500
5 /
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=83 Card=138 Bytes=4140)
1 0 VIEW OF 'index$_join$_001' (Cost=83 Card=138 Bytes=4140)
2 1 HASH JOIN
3 2 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX2' (NON-UNIQUE) (Cost=81 Card=138 Bytes=4140)
4 2 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX1' (NON-UNIQUE) (Cost=81 Card=138 Bytes=4140)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
272 bytes sent via SQL*Net to client
319 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
<b>I did have to HINT it since it is the WRONG plan:</b>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select object_name, object_id
2 from big_table
3 where object_name like 'ABCDEF%'
4 and object_id between 1000 and 1500
5 /
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=431 Card=138 Bytes=4140)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=431 Card=138 Bytes=4140)
2 1 INDEX (RANGE SCAN) OF 'BIG_TABLE_IDX1' (NON-UNIQUE) (Cost=52 Card=138)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
272 bytes sent via SQL*Net to client
319 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
No init.ora's really needed here.
Reader
A reader, April 05, 2002 - 2:32 pm UTC
Tom,
Is there a way to get the information from the
data dictionary, which latches(names of latches) are of
type IMMEDIATE_GET and which latches (names of latches)
are WILLING_TO_WAIT type.
Also what is the criteria for latches to change from
one type to another. Do they change from one level
to another also, in the priority scheme of things
Thanks
April 05, 2002 - 2:48 pm UTC
not that I'm aware of. Its the code in the database that decides whether its willing to wait or not.
index_join hint utilizes only one of the indexes
Vlado, May 05, 2004 - 5:53 pm UTC
I have a query with a index_join hint:
SELECT /*+index_join(ho idx1 idx2)*/
sum( nvl(mw,0) )
FROM ho
WHERE fko = :b4 AND
fks = :b3 AND
hdate >= :b2 AND
hdate < :b1
The resulting execution plan shows oracle 9.2.0.3.0 (AIX - 64BIT) is doing an index_join, but only utilizes one of the indexes I specified, and joins that one to another index. The problem is that the other index is not partitioned so the query takes longer to finish.
| Rows Row Source Operation
|--------- ---------------------------------------------------
| 345 SORT AGGREGATE (cr=6409065 pr=6730699 pw=340953 time=7952.32)
| 252288 .FILTER (cr=6409065 pr=6730699 pw=340953 time=7951.67)
| 252288 ..VIEW (cr=6409065 pr=6730699 pw=340953 time=7951.20)
| 252288 ...HASH JOIN (cr=6409065 pr=6730699 pw=340953 time=7950.17)
|154842493 ....PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=264615 pr=264615 pw=0 time=534.27)
|154842493 .....INDEX RANGE SCAN idx2 PARTITION: KEY KEY (object id 42026 ) (cr=264615 pr=264615 pw=0 time=316.62)
| 5228609 ....INDEX FAST FULL SCAN idx3 (object id 36664 ) (cr=6144450 pr=6125131 pw=0 time=6704.00)
My question is for what reason would Oracle pick only one of the two indexes in the index_join hint?
Below are the table, view & index definitions:
CREATE TABLE ho_t
(hid NUMBER(9,0) NOT NULL,
hdate DATE NOT NULL,
fko NUMBER(9,0) NOT NULL,
fks NUMBER(9,0) NOT NULL,
df NUMBER(1,0) NOT NULL,
mw NUMBER(13,3)
--... 25 more columns
)
PARTITION BY RANGE (hdate)
(
PARTITION p_2_2 VALUES LESS THAN (TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
--... 15 more partitions
)
/
CREATE OR REPLACE VIEW ho (
hid,
hdate,
fko,
fks,
df,
mw
--.. 25 more columns
)
AS
select hid, hdate, fko, fks, df, mw
--... 25 more columns
from ho_t
/
CREATE UNIQUE INDEX idx1 ON ho_t
(
hdate ASC,
fks ASC,
df ASC,
fko ASC
)
LOCAL (
PARTITION p_2_2
--... 15 more partitions
)
/
CREATE INDEX idx2 ON ho_t
(
hdate ASC,
fko ASC,
mw
)
LOCAL (
PARTITION p_2_2
--... 15 more partitions
)
/
CREATE INDEX idx3 ON ho_t
(
fko ASC,
fks ASC
)
/
ALTER TABLE ho_t
ADD CONSTRAINT pk_ho_t PRIMARY KEY (hid)
USING INDEX
/
ALTER TABLE ho_t
ADD CONSTRAINT fk_h FOREIGN KEY (fko)
REFERENCES R.s (oi) ON DELETE SET NULL
/
May 05, 2004 - 8:11 pm UTC
help me break this example:
drop table ho_t;
CREATE TABLE ho_t
(hid NUMBER(9,0) NOT NULL,
hdate DATE NOT NULL,
fko NUMBER(9,0) NOT NULL,
fks NUMBER(9,0) NOT NULL,
df NUMBER(1,0) NOT NULL,
mw NUMBER(13,3)
)
partition by range(hdate)
(
partition p1 values less than
(TO_DATE(' 2000-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
partition p2 values less than
(TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
)
/
create or replace view ho
as
select * from ho_t
/
CREATE UNIQUE INDEX idx1 ON ho_t
(
hdate ASC,
fks ASC,
df ASC,
fko ASC
)
local
/
CREATE INDEX idx2 ON ho_t
(
hdate ASC,
fko ASC,
mw
)
local
/
CREATE INDEX idx3 ON ho_t
(
fko ASC,
fks ASC
)
/
variable b1 varchar2(25);
variable b2 varchar2(25);
variable b3 varchar2(25);
variable b4 varchar2(25);
set linesize 121
delete from plan_table;
explain plan for
sELECT /*+index_join(ho idx1 idx2)*/
sum( nvl(mw,0) )
FROM ho
WHERE fko = to_number(:b4) AND
fks = to_number(:b3) AND
hdate >= to_date(:b2) AND
hdate < to_date(:b1)
/
select * from table(dbms_xplan.display);
re: index_join hint utilizes only one of the indexes
Vlado, May 06, 2004 - 1:37 pm UTC
To break the script I would have to copy the data from the original table (11+ millions rows) and see if I it behaves the same as the orginal table.
I was hoping you already encountered this problem before, i.e. any undocumented restrictions on the index_join hint so that's why I send you the indexes/table/view definitions...
May 06, 2004 - 8:20 pm UTC
no, use dbms_stats to set table stats and such to get my plan to become your plan.
If the index join *could* have been used -- it would have been used. there must be something you can do to make this test case "more like your real life" to achieve that.
A reader, February 08, 2005 - 11:59 am UTC
252288 .FILTER (cr=6409065 pr=6730699 pw=340953 time=7951.67)
I guess pr and pw stand for 'physical reads' and 'physical writes'. Are these 'pr' and 'pw' new in some latest oracle release ?
February 09, 2005 - 1:27 am UTC
9ir2, yes.
Regarding the 'useless' index referenced earlier....
Mike, February 11, 2005 - 9:36 am UTC
Is there a way to determine if Oracle ever (or never) uses a particular index on a table?
February 11, 2005 - 8:30 pm UTC
you can alter the index to be monitoring in software written this century.... (eg: 9i)
kavitha, May 09, 2005 - 3:35 am UTC
Hi
I have gone through bitmap indexes,Locally managed tablespaces. in locally managed tablespace i found the difference between dictionary tablespace and local here dictionary managed tablespace stores the free space and used space in data dictionary tables or undosegments locally managed tablespace stores in bitmap does it not consume any space how the bitmap appears how can you say it consumes space how does it look like? Please let me know
Kavitha
May 09, 2005 - 7:26 am UTC
couple of unrelated things in there.
A bitmap index and locally/dictionary managed tablespaces don't "go together" really.
a bitmap index is a type of index. It may reside in a tablespace that is either locally or dictionary managed. A bitmap index is not used to manage tablespace data in either case, it is just a type of index you may use to index your data.
In a dictionary managed tablespace, used extents (allocated extents) and free extents are managed in a set of database tables. Much like an application might manage its data. If an extent is used, it would appear in a sys table UET$ (used extent). If it is free, it would be in FET$ (free extents). The cost of moving an extent from FET$ to UET$ was expensive, especially if you do it alot.
So, enter a bitmap method of managing free space. Now instead of the database having a single extent pair of tables for each tablespace, each tablespace manages its own data in bitmaps (0 = free, 1 = allocated) at the 'front' of a tablespace. You get greater concurrency since each tablespace has the ability to manage its own storage and allocating/freeing extents becomes much easier.
latches and locks
Adam Lowry, May 09, 2005 - 8:38 am UTC
Operating system theory.
A semaphore used to prevent a race condition on an element of a data structure.
Would this not be a good place to start in explaining this concept?
May 09, 2005 - 9:14 am UTC
not sure where this thought is going?
latches and locks
A reader, May 09, 2005 - 10:02 am UTC
"Oracle uses atomic instructions like 'test and set' for operating on latches."
What is atomicity? What is the machine really doing? If one understands this concept one understands the "theory" behind locks and latches. IMHO without an understanding of atomic operations trying to understand locks and latches is wasted effort.
What does your C code become? assembly.
At the assembly level a single operation is not performed "all at once" and we can end up with incorrect values of shared variables.
Take this trivial C code.
b=5;
b = b + 1;
Say there are two threads thread1 and thread2 that share the variable b. Take this order of execution.
thread1) load b into some register in thread 1.
(thread2) load b into some register in thread 2.
(thread1) add 1 to thread 1's register, computing 6.
(thread2) add 1 to thread 2's register, computing 6.
(thread1) store the register value (6) to b.
(thread2) store the register value (6) to b.
We started with 5. Then two threads added one. The final result however is 6. Not the expected value of 7. The two lightweight processes messed with one another and produced the wrong result.
To solve the above "race condition" we need to give exclusive rights to the variable b while it is being modified. The process of gaining an exclusive right to something is called "locking".
May 09, 2005 - 10:10 am UTC
sorry -- i thought people would understand the term "atomic" and "atomic instruction".
an atomic test and set is generally provided by the machine, the hardware, the chipset. We use it. 'atomic test and set' means the 'test and set are one thing, atomic, they either both happen without interruption or neither does'.
In Oracle that process you refer to is known as LATCHING,
...
Latches and enqueues are lightweight serialization devices used to coordinate
multi-user access to shared data structures, objects and files. Latches are
locks which are held for very small periods of time extremely small periods,
for example the time it takes to modify an in memory data structure.
........
latches and locks
Adam Lowry, May 09, 2005 - 10:36 am UTC
thanx.
Materialized view
kavitha, May 24, 2005 - 8:16 am UTC
Hi tom,
thanks for your response and i have one more question
is there any impact on performance if we create materialized view? can you please tell me how is it?
Kindly let me know
Thanks in advance
Kavitha
May 24, 2005 - 10:14 am UTC
one of three things will happen:
a) some things will go faster
b) some things will go slower
c) some things will neither go faster nor slower
A materialized view (MV) can take a horribly slow query and apparently make it go faster (since the big query can read the small MV).
A materialized view could slow down a transactional system because of the presence of MV logs.
A MV might not be appropriate and might not make things faster or slower, just the same.
Think of a MV like you do an index, indexes can
a) make somethings faster
b) somethings slower
c) otherthings neither faster nor slower
just a tool
Have you read:
</code>
http://docs.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#721 <code>
How to get the size of single table row
Kavitha, September 12, 2005 - 3:32 am UTC
Hi Tom ,
Could please tell me how to get the size of table row
Thanks,
Kavitha
September 12, 2005 - 7:26 am UTC
add up the vsize() values of each column
MV Logs
kavitha, September 12, 2005 - 3:40 am UTC
Hi Tom,
If you dont mind can you please tell me about materialized logs
September 12, 2005 - 7:30 am UTC
A reader, September 14, 2005 - 7:04 am UTC
Tom,
the vsize() shows the column length but i want to get the row length is it possible to identify the row length
September 14, 2005 - 8:46 am UTC
add up the columns vsizes(), there is no "row_length()" function.
Kavitha, September 14, 2005 - 7:11 am UTC
Tom,
In my table date column i want to update the timestamp
actually the date it shows is 03-sep-2005 3:04:45
i want to update the timestamp to 8:06:05
is there any solution for this
Please let me know
September 14, 2005 - 8:48 am UTC
update t
set date_col = trunc(date_col,'dd') + 8/24 + 6/24/60 + 5/24/60/60;
is but one approach.
Kavitha, September 14, 2005 - 8:04 am UTC
Tom,
What is the difference between dbms_sql and execute_immediate?
How much time difference would be there in executing a sql statement by using dbms_sql and execute_immediate
Please let me know
Thanks in advance
Kavitha
September 14, 2005 - 9:00 am UTC
execute immediate is an easy way to execute a one time dynamic sql statement.
dbms_sql allows you to parse a statement once and bind/execute it over and over and over.
In 9i and before, if you:
for x in ( something )
loop
execute immediate 'insert into x values ( :x )' using ....;
end loop;
that will be like:
for x in (something)
loop
parse the insert statement
bind it
execute it
close it
end loop
using dbms_sql, you can code instead:
parse the insert statement
for x in ( something )
loop
bind it
execute it
end loop
close it
In 10g, that is what will happen with execute immediate as well -- assuming you re-execute the same statement, the logic is like this:
for x in ( something )
loop
if (last_parsed_statement <> current_statement to execute)
then
close old statement
parse new statement
last_parsed_statement = current_statement
end if
bind it
execute it
end loop
In that fashion, execute immediate is as efficient for doing repeated dynamic sql as dbms_sql was.
dbms_sql is still very useful however, even in 10g. You cannot process an arbitrary select with execute immediate (you have to know at compile time the number of columns and their types).
So, dbms_sql is still useful for writing generic utility code.
But in general, use execute immediate
UNLESS you are in 9i and before AND you repeatedly execute the same statement over and over and over again.
ANSII standards in Oracle
Kavitha, September 20, 2005 - 1:43 am UTC
Hi Tom
what are ANSII standards in Oracle Could you please explain me with example
September 20, 2005 - 10:04 am UTC
You have it a bit backwards.
There are ANSI/ISO standards that define many things - one of which is the database language SQL.
We attempt to have our sql dialect as close the ANSI standard as reasonable (it is a huge standard these days)
For exmaple, in Oracle 9i we added "LEFT OUTER JOIN", "FULL OUTER JOIN" and similar syntax for performing joins -- as per the ANSI SQL specification.
About Hints
Bipin Dalal, September 30, 2005 - 6:49 pm UTC
Tom,
This particular topic helped me a lot in making my point. Thanks!
But I still have a question for you. This is what happened:
we have a single table (very big) search queries. SQl is dynamically formed. We dont use bind variables as data is really skewed. But we do build histograms on ALL indexed columns (this may not be the best thing but someone else is deciding this) as well as compute stats periodically (weekly). we are using 9.2.0.4
(We ran tests just after building histograms and gathering compute stats.)
My assumption was optimizer had enough information to come up with a good plan by merging conventional indexes. But it did not and hence bad plan.
I used index_join hint on SQLs and it came up with a 100x times better plan by joining underlying indexes. (reduced LIO to 1000 from 100000)
My question is have you observed such optimizer behavior? Why even after constructing histograms (with 200 buckets) on indexed columns and fully analyzing table and indexes, optimizer will come up with bad plan?
I know this may sound vague question to you but I will still post it. What area do you think may have problem?
1. histograms
2. dbms_stats
3. optimizer
4. any other?? (viz. my understanding)
Hints do work but since SQL is dynamically generated it is extremely complex to use hints.
THanks in anticipation.
-Bipin.
October 01, 2005 - 8:51 pm UTC
insufficient data - I don't know your tables, I don't know the number of distinct values, I don't know if 200 was the right number...
What Data I need to send
Bipin, October 03, 2005 - 4:12 pm UTC
I understand. I am not sure what is the optimal data I can supply. I can supply the data whatever is required.
My original question was if histograms are built, indexes are compute analyzed, why optimizer needs a hint?
THanks for your response.
October 03, 2005 - 8:59 pm UTC
well, how can i say that 200 is "correct" if I don't even know the data?
I can say, this book
</code>
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html <code>
will help you understand what we need to understand to help answer this question
THanks but...
Bipin, October 04, 2005 - 2:11 pm UTC
Thanks for suggestion. I think that's what I will have to do. Unfortunately this book is not published yet? (that's what apress site says). But i will keep an eye.
I tend to think there is something wrong with the way we build histograms/stats or the way optimizer uses that information. And for understanding that, I am sure I need to understand the data optimizer uses.
Thanks!
October 04, 2005 - 6:23 pm UTC
It is coming out in a month.
Increase Performance PL/SQL code.
Vijay, July 31, 2008 - 10:50 am UTC
Hi Tom,
Could you pls suggest me tips how to increase performance in pl/sql code.
pl/sql code contains:
variable, cusors, calling functions, calling sub-processes and inserting data into tables.
Thanking you in Advance.
Regards,
Vijay.
August 03, 2008 - 1:29 pm UTC
write as little as possible...
That is written in all seriousness. The less you write, the faster it'll go - pack everything you can into SQL and write as little procedural code as humanly possible.
So, in order to write blazing fast plsql, learn SQL better than anyone you know and exploit it fully.