Creating an index

[ermagarden]

I have a sql statement that takes 2 minutes to run. I created indexes and now it's down to 36 seconds. I run the sql thru iSeries Navigator. The "index advisor" says to create a "Binary Radix" index.

in iseries Navigator, when I do a "new index", I dont see Binary Radix in the dropdown list.

So I tried this:
CREATE Binary Radix INDEX D2Index

ON TB2(myColumn)

But this doesnt work either. Get an error that "Binary" is invalid .

So how do I create this Binary Radix index??

[B.Hauser]

Re: Creating an index

Contrary to other database on DB2 UDB there are 2 different kinds of index.

1. Binary Radix Tree Index
2. Encoded Vector Index

Binary Radix Tree Index is the same kind of access path that other data bases also support and which is part of the SQL standard. Encoded Vector Index is a special kind of access path patented by IBM.

If you want to create a binray radix tree index just use the CREATE INDEX command:

CREATE INDEX MySchema/MyIndex On MyTable (Key1, Key2, ... KeyN) 

Birgitta

[ermagarden]

Re: Creating an index

ok, i'll try it. I have one table and whatever index I try, then I run my sql, I look at the "Visual Explain"...it just wont pick up the index...

I ran more tests now and I have a "Group By" that slows the query down and nt picking up the index...it does a table scan...

I can post the sql here and see if you or anyone else can help me out...

post it?

[ermagarden]

Re: Creating an index

Created the index but still nope, the index is not picked up and causes a table scan....
I can post my sql ....

[arrow483]

Re: Creating an index

If the SQE engine determines that most of the records will be needed, it will choose table scan over index. Basically, it estimates that it can table scan will run faster than than the time saved by the index.

If you look at Visual explain, it gives the reason why it choose to scan. Also, if you run STRDBG and then run the query, the joblog will also show why index wasn't used.

[B.Hauser]

Re: Creating an index

Hi,

just a few questions:
1. On which release are you working?
2. Which Query Engine is executing the Statement?
If it is the CQE and you are on Release V5R4 why the SQE is not used?

Indexes may also be adviced and only used for collecting statistics.
If the SQL statement is executed by the CQE (and I assume it is) , the index advice is based on estimations, that may not represent excatly the real data composition. In this way the optimizer may not use the adviced access path.

It the SQL statement is executed by the CQE, try to get it executed by the SQE.

Birgitta

[ermagarden]

Re: Creating an index

1. The explanation is this: Optimize chose table scan over avaliable indexes.

2. My iSeries Navigator is V5R4. I open "Run An SQL Command" and run the SQL from there. I think the AS400 is V5R3 ...i dont know how to check that but I think we're on the latest release.

3. The same SQL works fine in SQL Server but on that, the vendor has lots of indexes

4. The AS400 DB does have "logicals"

5. I have a "group by" and joining 2 tables because i have to get SUM of a column. One table has 800,000 something rows and the other has 700,000 rows. When I remove the group by and do a straight select on TB1, it's fast. It's when I add the Group By back and do the inner join ..that it's taking a long time to run...I'm doing Fetch first 20 rows only

I was thinking about breaking the SQL up: first get the rows from TB1. Dump into a temp table. Then join the temp table to TB2 and get teh SUM. ..

I dont know, been working on this for 4 days now!

[arrow483]

Re: Creating an index

If any of the logicals have select/omit in them, the system will use the CQE engine. Check if you have a file in QUSRSYS called QAQQINI. If not, then run

 CRTDUPOBJ  OBJ(QAQQINI) FROMLIB(QSYS) OBJTYPE(*FILE) TOLIB(QUSRSYS) DATA(*YES) 

Then modify QUSRSYS/QAQQINI with value IGNORE_DERIVED_INDEXES and set it to *YES (If it doesn't already exist in the file, add it)

This allows SQE to ignore the logicals it can't use, and try to run the query with SQE.

[ermagarden]

Re: Creating an index

Now, if I do this on our system,...when I give the app/sql to clients...they have to do the same on their AS400...right?

[arrow483]

Re: Creating an index

Well ... you don't have to -- but it will prob help performance if you do. Bear in mind the the SQL engines are based on current data. So a system with "small" files may not use the same access methods as a system with "large" files.

Here's what I found out about this QAQQINI option:

1. SQL processor looks at the query and decides it could use SQE.
2. SQE looks at indexes and LF
3. SQE finds a derived index (such as a select/omit LF)
4. SQE can't use this LF, so rejects query and sends it back to CQE
5. CQE analyzes the access paths again (but is limited to how many it will check, so might not find the best one anyway.)
6. CQE decides how to run.

Now, with the IGNORE specified.

1. SQL processor looks at the query and decides it could use SQE.
2. SQE looks at indexes and LF
3. SQE finds a derived index (such as a select/omit LF), but ignores it
4. SQE decides how to run he query

In my experience (varies by system, files, data), I've seen as much as 400% improvement using SQE over CQE. In addition, if it gets kicked back to CQE there is about 15% performance penalty, since it has to recheck the access paths. To me, the possible benefit of having a LF with the select/omit that exactly matched the query needs is far outweighed by the performance boost of SQE.

[ermagarden]

Re: Creating an index

Thanks for the explanation. My issue is with large data...

I do a select, one of the clauses is " where....and PhoneField like '314%' "

There are 114000 something rows starting with 314. Even tho I do "fetch first 20 rows only"..it takes time to bring the 20 rows back...

Now If I do "314225%"...it's very quick because the resultset the database has to go thru is smaller...

let me look into the commands you have and will post back...

[arrow483]

Re: Creating an index

FYI: When you do a query with 114,000 hits and a fetch 20 rows, the query still runs on the system and finds the complete 114,000 records. The fetch only puts the first 20 in the resultset, but the full query runs anyway.

You might help performance by making sure PhoneList is in the primary "from" file and not a joined file, if possible (maybe it already is).

[ermagarden]

Re: Creating an index

TB1 has the field PhoneField

I join to TB2

so : select .... from TB1 inner Join TB2 ON TB1.Field = TB2.Field
where exists (.....)
And TB1.PhoneField like '314%'
and TB1.Field2 <> 'D'
and TB2.Field2 <> 'D'
Group By ....
Order by PhoneField
Fetch First 20 rows only

[arrow483]

Re: Creating an index

select .... from TB1 inner Join TB2 ON TB1.Field = TB2.Field
where exists (.....)
And TB1.PhoneField like '314%' 
and TB1.Field2 <> 'D'
and TB2.Field2 <> 'D'
Group By ....
Order by PhoneField
Fetch First 20 rows only 

But you DO have "TB2.Field2 <> 'D'" in the where clause. So the system will need to join all records meeting the TB1 criteria to TB2 to see if "TB2.Field2" is a "D". Probably not much you can do about that, unless TB2 is much larger file than TB1.

[ermagarden]

Re: Creating an index

Yes, TB2 is larger than TB1...

And I'm not sure what you mean by your comment :
But you DO have "TB2.Field2 <> 'D'" in the where clause. So the system will need to join all records meeting the TB1 criteria to TB2 to see if "TB2.Field2" is a "D". Probably not much you can do about that, unless TB2 is much larger file than TB1.

I want to pick up rows from TB2 with Field2 not equal to 'D'...