Sitemap

Amicuk Programming Answers

Not In Too Slow

-0001-11-30   Views:0

Advertisement

Hello. I have a simple performance problem. It's a common configuration error to have a user account, but no extended information about them. To help administrators identify this, I've created two views. Anyone who is in view 1, but not view 2 is a p

Hello. I have a simple performance problem.
It's a common configuration error to have a user account, but no extended information about them. To help administrators identify this, I've created two views. Anyone who is in view 1, but not view 2 is a potential problem, so I'd like to show them to administrators in a report.
The problem is that the statement:
select user_name from view1 where user_name not in view2;
is a real dud. It rarely returns in a reasonable amount of time. Table 1 has about 6000 entries and view2 has about 500 entries.
How should I be doing this?

The replay answer
Advertisement
Kevin:
You raise a good point. If the queries underlying the views are slow, then optimizing this query is probably not going to help much, if at all.
Sri:
Which is better, depends on a number of factors. I think that some of the most important factors are:
1. Absloute sizes of the two tables
2. The relative sizes of the two tables
3. CBO versus RBO
4. Version of Oracle
5. Which columns are being compared.
Number 5 may need a little clarification. If the question is find all PK's in table1 which do not exist in table2, then I would more likely use NOT EXISTS or perhaps OUTER JOIN. However if the question is find entire rows in table1 that do not have a corresponding entire row in table2 then I would be more likely to use MINUS.
If we assume that there are no criteria used agains either table (which might affect the choice of indexes), then all methods will require at least one full table scan (at best a fast full index scan), and sometimes two. So, the chief factor affecting performance is the method Oracle uses to get the rows in table1 that do not exist in table2. On a conceptual basis, this is how I understand the various approaches operate. This is almost certainly not the exact algorithm that Oracle uses.
The NOT IN will do a full scan of table2 to get a list of values, then for each row of table1 scan this LOV to see if it finds a match (FILTER operation). If not, add the record to the result set. If table two is absolutely small (e.g. state_code table with 50 records) then this may well be the fastest approach since scanning the LOV should be fast in memory.
MINUS does a full scan of both tables, sorts each, then scans the two lists together to identify mismatches (MINUS operation). If the two tables are relatively similar in size, then this is likely to be one of the faster methods. The optimizer may choose to use index scans if the columns being compared are all indexed. If you are trying to compare all columns, or a large (unindexed) subset, then this is likely to be the fastest method. It may also be faster if you expect a relatively large number of mis-matched records.
In a NOT EXISTS (or EXISTS), for each row in table 1, the correlated sub-query is executed against table2. If the all of the indentity columns in the sub-query are indexed, then this is pretty quick. The relative sizes of the tables do not matter much, but if both are large, this may be a little slower. Also, it is not as efficient when you need to look at values in several columns. If table2 is not appropriately indexed, and it is large, then this could be incredibly slow since it will require a full scan of table2 for each row in table1.
The OUTER JOIN approach uses the expected SORT JOIN - MERGE JOIN or SORT JOIN - NESTED LOOP approach (depending on the version and optimizer mode). This is pretty efficient, particularly if the two tables are large, and relativley equally sized, and you expect a relativley small number of mismatches.
There is also a fifth approach.
In sqlplus run $ORACLE_HOME/rdbms/admin/utlexcpt.sql then
ALTER TABLE table1
ADD constraint t1_t2_fk
FOREIGN KEY (column_list) REFERENCES table2 (column list)
EXCEPTIONS INTO exceptionsThen deal with the error rows you get in the table exceptions. This requires a unique index on column_list in table2.
Probably not useful for a one-off excercise, but it will prevent it happening on an ongoing basis.
John

Go to See the other 15 answers

Application running slow in ver 1.5, 1.6 but not in 1.4

Category:DefaultRelease time:2015-10-11Views:130

We have a application that was running very slow when we used any version sun java instead of MS java in IE6 SP2. I removed MS Java from IE and installed Sun java 1.4.2_16 and the applications starts in under 10sec's. But we had to test the other ver[More]

Mid 2011 Macbook Air Painfully slow.

Category:DefaultRelease time:2015-10-11Views:130

Mid 2011 Macbook Air Painfully slow. Hey everyone.  I am writing about my 13 inch Mid 2011 Macbook Air.  Recently it has been extremely slow and on occasion has not started up. I have reset the PRAM and SMC already, yet it is still very slow.  I have[More]

Internet Connection Became Slow after Introduction of Cisco ASA 5505 to the Network

Category:DefaultRelease time:2015-10-11Views:130

I configured a Cisco ASA 5505 (Version Cisco Adaptive Security Appliance Software Version 7.2(3) Device Manager Version 5.2(3) in transparent firewall mode and inserted after Cisco 1700 router. However, the internet connection became very slow and us[More]

New FiOS GigE Wireless 'N' Router has slowed our wireless speed to a crawl...need help!

Category:DefaultRelease time:2015-10-11Views:130

This past Tuesday I installed the new N router.  Also, we upgraded to FiOS Quantum 75/35 (previously 15/5).  That was activated the following day.  I can't complain about the internet speed to our pc since it's wired.  Speed tests showed we were gett[More]

Since installing LTR 5.4, which I've now upgraded to 5.6, I've encountered repeated slowness and malfunctions in operations, especially when using the Compare View function and the Tab key to open and close the right and left side panels.  Such problems n

Category:DefaultRelease time:2015-10-11Views:130

Since installing LTR 5.4, which I've now upgraded to 5.6, I've encountered repeated slowness and malfunctions in operations, especially when using the Compare View function and the Tab key to open and close the right and left side panels.  Such probl[More]

Since upgrading to FF4, my web applications and browser start-up time have been slower, instead of faster as the upgrade promises. Any suggestions?

Category:DefaultRelease time:2015-10-11Views:130

I feel like my web apps have been slowing down ever since the plug-in container was introduced. With the previous version of FF, I would have to close the browser and re-open it periodically to ensure that apps ran smoothly. Now, even that doesn't wo[More]

Report Performance is slow

Category:DefaultRelease time:2015-10-11Views:130

Hi In our production one report is running very slow, for one moth its taking 10 to 12 min earlier it was took only 2 min for month data, we are confused why itu2019s happening. Query is running on multiprovider. On cube we are using only one aggrega[More]

Open and Save dialogs are painfully slow. Is there a solution?

Category:DefaultRelease time:2015-10-11Views:130

Open and Save dialogs are painfully slow. Is there a solution?I pulled this part out of one of the topics Barney-15E linked to. It absolutely works! The glacially slow Finder response has been driving me batty in Mavericks. Each time I restarted, it[More]

.gif preview slow in Save for Web

Category:DefaultRelease time:2015-10-11Views:130

I am having an issue in Save for Web where the .gif animation preview works very slow and choppy. I am an advertising designer and many times I need to work on animated .gif banners. I am working with Photoshop CS6. When I play the animation in the f[More]

Discoverer Report Slow

Category:DefaultRelease time:2015-10-11Views:130

Hi All, I have a problem wherein some specific users are saying that the report is slow at their end. The exact issue is...we have Discoverer Report present at the US server. Now we have 2 sites CANADA and US from where this report is run. Both sites[More]

Mail and contacts wont open on imac  also runs slow

Category:DefaultRelease time:2015-10-11Views:130

Mail wont open  contacts wont open  rus very slow   helpBoot from your recovery partition (command-R at startup) and select Get Help Online. Safari will launch. Is it slow?Read other 2 answers[More]

Hot
Hi evryone, I'm a final year software engineering student,I'm looking for dynamic,innovative final year project based on J2EE technologies. I'll be looking forward for ideas/suggestions from the community members.Install Eclipse, tomcat, axis, logger [More]
Problem description: I got a bunch of MOV files on a HFS+ formated disk for editing on Premiere Pro CS5 (5.0.3). When I import to Premiere, Premiere says "Codec missing or unavailable". Paragon HFS+ is used to handle the HFS+ formated disk. I co [More]
Any help is appreciated.  Followed the photomerge panorama instructions and got error 48: file or folder not found.....line 16 ... using photoshop elements 10 on imac with OSX 10.8.2 ThxHave you recently updated your Operating system? Please try repa [More]
Dear Guys, Let me know implications on business when tax procudure changed from TAXINJ to TAXINN ans also how to do the configuration, send me some documents Regards: Tata ReddyHi, Pls dont go for this type beacause User's familiar with one procedure [More]
my apple tv 2nd gen is moving at a snail's pace at step 1, still preparing update.  What do I do?So long as it's still progressing, I'd be inclined to leave it alone, however if it is stuck and clearly no longer progressing..... You can't do much els [More]
Every time I make changes in Creator 2.1 it appears to add a blank link between every data line in the associated java file.. This was (and still is even in Sea Monkey) a problem with Mozilla Composer every since Netscape (Composer) 4.x.. way back wh [More]
Sir, I encrypt the role password using dbms_obfuscation package. Can I enable a role using such a password before decrypting it ? Thanks a lot Stephendbms_obfuscation does not create passwords. A password is not something you can decrypt ! At least u [More]
Just downloaded Elements 11 and am extremly disappointed. What is the oldest version of Photoshop that I can run on Mountain Lion? ThanksPSE 6.Read other 2 answers [More]
Hi, I have a G5 that came preinstalled with 10.3 and the iLife programs (iphoto, iDVD, etc). Over time, I upgraded to 10.3.9. Recently my computer was having problems, so I did an erase and install from the original Software install and restore DVD t [More]
Hi everybody, what's wrong with this expression: If ( stringindexof( Local.string , Local.search_string ) == 0 , true , false) I get an "Invalid variable in expression If" error. Isn't it possible to connect functions together? or the more spect [More]