SQL Optimization Question - Which Is Faster/Better? 1

[kjv1611]

As mentioned in a couple other recent threads, I'm extremely new to Oracle, and I'm attempting to make some Access databases be much more efficient in pulling their data from an Oracle database server.

One question I have with this is in the SQL structure for creating a table.

Basically, we have an varchar field and a date field which we need to compare for a report pulled via Access. Currently, another large query is run, a table built (millions of records), then another query runs and deleted 99.9999% of the records. So, to me it would seem best to take care of the largest differentiating factor up front.

Here is the query before I add the part that is currently in a separate delete query (this part works for sure):

CREATE TABLE MyTestTable AS 
SELECT	 a.BrNum ,a.Item ,a.STATUS ,a.UT4 ,a.UN5 ,a.UT3
		,r.NAME ,r.SS_NO 
		,b.DID ,b.TID ,b.Ch ,b.NumFld
		,b.DtFld ,b.CID ,b.DDt ,b.DSDt
FROM	SysNm.Prts p
			INNER JOIN
		SysNm.AcctMain a
			ON p.BrNum = a.BrNum
			INNER JOIN
		SysNm.AcctRel r
			ON a.Item = r.Item
			INNER JOIN
		SysNm.Rbkp b
			ON a.Item = b.Item
WHERE 	(p.PT <> '125') AND
		(a.BrNum <> '50000') AND 
		(a.Resp = 'Mouse1') AND 
		(a.DID = 'Mouse1') AND
		(b.DID = 'Mouse1');

Then I initially was thinking of adding in the delete portion, well being the opposite of the delete query's criteria...

CREATE TABLE MyTestTable AS 
SELECT	 a.BrNum ,a.Item ,a.STATUS ,a.UT4 ,a.UN5 ,a.UT3
		,r.NAME ,r.SS_NO 
		,b.DID ,b.TID ,b.Ch ,b.NumFld
		,b.DtFld ,b.CID ,b.DDt ,b.DSDt
FROM	SysNm.Prts p
			INNER JOIN
		SysNm.AcctMain a
			ON p.BrNum = a.BrNum
			INNER JOIN
		SysNm.AcctRel r
			ON a.Item = r.Item
			INNER JOIN
		SysNm.Rbkp b
			ON a.Item = b.Item
WHERE 	(p.PT <> '125') AND
		(a.BrNum <> '50000') AND 
		(a.Resp = 'Mouse1') AND 
		(a.DID = 'Mouse1') AND
		(b.DID = 'Mouse1')[HIGHLIGHT] AND
		(LEFT(b.NumFld,2 <> RIGHT(b.DtFld,2))[/HIGHLIGHT];

But then I got to thinking... what if I cut that rather large portion off up front and then compared THAT with the remainder of the query... I'd think that would be the most beneficial... So, here's my idea to do it that way:

CREATE TABLE MyTestTable AS 
SELECT	 a.BrNum ,a.Item ,a.STATUS ,a.UT4 ,a.UN5 ,a.UT3
		,r.NAME ,r.SS_NO 
		,b.DID ,b.TID ,b.Ch ,b.NumFld
		,b.DtFld ,b.CID ,b.DDt ,b.DSDt
FROM	SysNm.Prts p
			INNER JOIN
		SysNm.AcctMain a
			ON p.BrNum = a.BrNum
			INNER JOIN
		SysNm.AcctRel r
			ON a.Item = r.Item
			INNER JOIN
		[highlight](
			SELECT	x.DID ,x.TID ,x.Ch ,b.NumFld
					x.DtFld ,x.CID ,x.Ddt ,x.DSDt
			FROM	SysNm.Rbkp x
			WHERE	(LEFT(b.NumFld,2 <> RIGHT(b.DtFld,2))
		) b[/highlight]
			ON a.Item = b.Item
WHERE 	(p.PT <> '125') AND
		(a.BrNum <> '50000') AND 
		(a.Resp = 'Mouse1') AND 
		(a.DID = 'Mouse1') AND
		(b.DID = 'Mouse1');

I've not tested either of the last 2 scripts so far... have a meeting, and then after that I hope to test the latter. I'm thinking it'll be the winner of the options, but I'd like any critiques or suggestions any of you might have.

Thanks.

 

[Dagon]

I suspect it will make no difference at all, but the way to test that is to use Oracle's explain plan utility. It will give you the execution plan of both statements and you can compare them. If they are identical, then you can conclude it's just a matter of personal preference which format you use.

Do a search for "Oracle explain plan" and you should get plenty of information.

For Oracle-related work, contact me through Linked-In.

 

[kjv1611]

Thanks! I'll look into that and see.

Then, I suppose, I can test running the larger filter first (by itself) into a new table, and then querying off of that table for the rest...

I'll test and see what I come up with... will post back.

Here's one reference I found on the above topic so far:

http://www.adp-gmbh.ch/ora/explainplan.html

Looks pretty informative so far.

 

[kjv1611]

Here's another reference I found which mentions another related function:

http://www.dbspecialists.com/files/presentations/use_explain.html

The other function/command (or whatever is the correct term for it) is TKPROF. So I'll see if that's of any help as well.

 

[kjv1611]

Well, I'm testing with the above mentioned commands/functions.

I've been able to load some details into the PLAN_TABLE, but can't then see those details via Access, so I am assuming either I need to COMMITT something or else it just isn't viewable externally... or via a different user.

Is there some way to get the results from the PLAN_TABLE (or another table created to store the same values) to a more reader-friendly location/format? For instance, maybe exporting/saving the results to a text file on my local drive? Any possibilities there?

I'm using SQL Plus as the application to test with the SQL statements at the moment, in case that helps.

 

[Dagon]

I believe you have to commit after running the explain plan, but the plan_table itself is an ordinary table and you should be able to view it anywhere once the data has been committed.

For Oracle-related work, contact me through Linked-In.

 

[kjv1611]

Yeah, that's weird/odd...

I ran the EXPLAIN PLAN FOR MySqlStatement, it inserted 9 rows into the PLAN_TABLE table. I could list them out in SQL Plus, but couldn't see the records in Access. Hmm... I'll look again... I wonder if there are more than 1 Plan_Table - possibly one for each schema/user/owner? Forgive me if I use the wrong keywords here.

I'll look and post back with my findings...

 

[kjv1611]

Nope, just looked, only one instance of PLAN_TABLE

 

[kjv1611]

One note of interest that I learned while testing for performance: the query (the 2 untested versions) would not have run at all.

I again was just assuming the LEFT() and RIGHT() functions would be the same.

Now I know to use:
SUBSTR(String,1,CharCount) for LEFT and
SUBSTR(String,Length(String)-CharCount) for RIGHT

And I suppose if I wanted to use something like MID, it'd be:
SUBSTR(String,NumStart,CharCount).

So, if the string is Monkey, and I want to return the first 3 characters, it'd be:
SUBSTR('Monkey',1,3)
--would give Mon as result

If I wanted to return the last 3 characters, I'd use:
SUBSTR('Monkey',LENGTH('Monkey') - 3)
--would give key as result

If I wanted the middle 2 characters, then I'd use this:
SUBSTR('Monkey',3,2) --If I knew the length

Or else if I didn't know the length, I'd need some combination of functions to find the half of the count, but round the result to an Integer rather than double/decimal format, and count up one from that..

Okay, I'm going far and away from anything I'd need right now... But I could remember such in case I wanted to look for a specific character in a string, and return the characters before or after it..

Well, that's just one more thing I've picked up in the day.

 

[SantaMufasa]

If I wanted to return the last 3 characters, I'd use:

SUBSTR('Monkey',LENGTH('Monkey') - 3)

--would give key as result

Correct, but this is simpler:

select substr('Monkey',-3) "Last 3" from dual;

Last 3
---------
key

If I wanted the middle 2 characters, then I'd use this:
SUBSTR('Monkey',3,2) --If I knew the length

Yes, but if you needed to extract the Middle 'N' characters on a regular basis (regardless of length), then you could build a user-defined function and use it in your queries:

create or replace function mid (str_in varchar2, req_len number)
                      return varchar2 
is
    margin  number;
begin
    margin := ceil((length(str_in)-req_len)/2);
    if margin < 1 then return str_in;
    end if;
    return substr(str_in,margin,req_len);
end;
/

Function created.

select last_name,mid(last_name,3) Middles from emp;

LAST_NAME       MIDDLES
--------------- -------
Ngao            Nga
Nagayama        gay
Quick-To-See    k-T
Ropeburn        peb
Urguhart        guh

select last_name,mid(last_name,5) Middles from emp;

LAST_NAME       MIDDLES
--------------- -------
Ngao            Ngao
Nagayama        agaya
Quick-To-See    ck-To
Ropeburn        opebu
Urguhart        rguha

Let us know if this is helpful.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[kjv1611]

Thanks. Let's just hope I don't have to pull the middle chars from string values any time soon.

 

[kjv1611]

Santa,

Thanks a TON on mentioning the better way to do handle the RIGHT() function. Frankly, it didn't work exactly correctly the way I was using it (from some other postings I found online). I was needing the last 2 characters of a string, but it was pulling the last 3 characters.. though I said LEN(string)-2... There's probably a good reason for it that I'm clueless on, but regardless, I now know the best way. So thanks again!