1

Topic: Index usage

Oracle 8
There is a table with field TIMESTAMP
There are two indexes normal "TIMESTAMP" and an index on the basis of function value "TRUNC (TIMESTAMP)"
Request
Select
t.* From ORDHIST t
Where
t. TIMESTAMP between to_date (' 20160331 ', ' YYYYMMDD ') and to_date (' 20160331235959 ', ' YYYYMMDDHH24MISS')

It is fulfilled with index usage for 5 seconds
Request
Select
t.* From ORDHIST t
Where
trunc (t. TIMESTAMP) = to_date (' 20160331 ', ' YYYYMMDD ')

It is fulfilled with the full table scan for 3 minutes
How to "force" the optimizer to use an index "TRUNC (TIMESTAMP)"
?

2

Re: Index usage

P.S.
hint
Select - +ORDHIST NK_ORDHIST_TIMESTAMP_DAY
Does not help

3

Re: Index usage

. .;
Excuse, it is possible more in detail

4

Re: Index usage

CTmuB;
Values of parameters OPTIMIZER_MODE, query_rewrite_enabled and query_rewrite_integrity show. Well and DDL an index result.

5

Re: Index usage

D;
https://docs.oracle.com/cd/B19306_01/se … htm#i17496
16.1.2.2 Hints for Access Paths

6

Re: Index usage

CTmuB;
And also the information on statistics presence under the table and its indexes.

7

Re: Index usage

CTmuB wrote:

D;
https://docs.oracle.com/cd/B19306_01/se … htm#i17496
16.1.2.2 Hints for Access Paths

well and check - as it is required to set  and as you wrote it.... Where keyword INDEX?

8

Re: Index usage

. . wrote:

CTmuB;
Through dbms_xplan, look on "Predicate Information (identified by operation id)"
DBMS_XPLAN.Examples

it in the eight? And anything, what dbms_xplan was "was introduced in Oracle 9i as a replacement for the" utlxpls.sql ""?

9

Re: Index usage

10

Re: Index usage

http://prntscr.com/gnj2qr
http://prntscr.com/gnj31y

11

Re: Index usage

And indexes
http://prntscr.com/gnj4b2

12

Re: Index usage

D wrote:

it is passed...
smile wink

13

Re: Index usage

D;
yes passed, but all the same
http://prntscr.com/gnj96i
http://prntscr.com/gnj9r7

14

Re: Index usage

CTmuB;
With OPTIMIZER_MODE it is clear. And as about

  - Eh wrote:

values of parameters query_rewrite_enabled and query_rewrite_integrity show.

???

15

Re: Index usage

Kind  - Eh;
I do not see such
http://prntscr.com/gnjb5o

16

Re: Index usage

CTmuB;
These are parameters ....
To look in unit.ora it is necessary. Well or we request to a blizzard v$parameter...

17

Re: Index usage

Kind  - Eh;
http://prntscr.com/gnjhec

18

Re: Index usage

CTmuB;
Try to execute to begin with in the session commands:
alter session set query_rewrite_enabled = true;
alter session set query_rewrite_integrity = trusted;
Then - try to fulfill the request to the table with FBI

19

Re: Index usage

Kind  - Eh;
The same result
P.S.
Values should exchange?
http://prntscr.com/gnjm2y

20

Re: Index usage

CTmuB;
In what GUI scripts you drive? Some fulfill each request in separate session.
To outwit such  - try to make alter system instead of alter session

21

Re: Index usage

  - Eh wrote:

try to make alter system instead of alter session

Reminded:

wrote:

' caused Ilya Muromtsa on duel. Also speaks to seconds: draw to it a dagger on a breast, I there will prick it with the true sword.
Ilya Muromets: pour its chalk, and give to me .

The HARDWARE: it is not necessary to follow this advice.

22

Re: Index usage

andrey_anonymous;
It is possible  as much as necessary, but on the eight without setting of these parameters operation with FBI does not fly up. Same even in the documentation as preliminary requirements it is described....

23

Re: Index usage

  - Eh wrote:

andrey_anonymous;
It is possible  as much as necessary, but on the eight without setting of these parameters operation with FBI does not fly up. Same even in the documentation as preliminary requirements it is described....

now word-for-word all points I will not recall, but something was exact type such:
1) install parameter such in value such
2) install parameter another in value another
3) be convinced that is used CBO as c FBI he is able to work only. RBO has a rest.
4) Produce to itself privilege QUERY REWRITE.
Now it is possible to start creation and usage FBI

24

Re: Index usage

  - Eh wrote:

andrey_anonymous;
It is possible  as much as necessary, but on the eight without setting of these parameters operation with FBI does not fly up. Same even in the documentation as preliminary requirements it is described....

Matter is not in .
The matter is that setting of these parameters at level of all DB can give the side special effects, which HARDWARE does not expect and to which is not ready.
It is better to explain how to adjust it , if business in it. And at the same time - to teach  banal text  instead of pictures... Preferably from sqlplus.
And if at the HARDWARE all turns out as is dreamed from it FBI (that, by the way, at all the fact) - that already to think, whether costs  manufactures and how to prepare system for new happy life.

25

Re: Index usage

Kind  - Eh;

wrote:

try to make alter system instead of alter session

Thanks helped
Only it is possible the link on

wrote:

Same even in the documentation as preliminary requirements it is described....

And what it

wrote:

can give side special effects

?

Posts [ 1 to 25 of 36 ]

Pages 1 2 Next

You must login or register to post a reply