Oracle Database 11g SQL Tuning

In this chapter, you will do the following:

• Learn about SQL tuning
• See SQL tuning tips that you can use to shorten the length of time your queries take to execute
• Learn about the Oracle optimizer
• See how to compare the cost of performing queries
• Examine optimizer hints
• Learn about some additional tuning tools

Introducing SQL Tuning

One of the main strengths of SQL is that you don't have to tell the database exactly how to obtain the data requested. You simply run a query specifying the information you want, and the database software figures out the best way to get it. Sometimes, you can improve the performance of your SQL statements by "tuning" them. In the following sections, you'll see tuning tips that can make your queries run faster; later, you'll see more advanced tuning techniques.

Use a WHERE Clause to Filter Rows

Many novices retrieve all the rows from a table when they only want one row (or a few rows). This is very wasteful. A better approach is to add a WHERE clause to a query. That way, you restrict the rows retrieved to just those actually needed.

For example, say you want the details for customer #1 and #2. The following query retrieves all the rows from the customers table in the store schema (wasteful):

-- BAD (retrieves all rows from the customers table)
SELECT *
FROM customers;
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- --------- ------------
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212
3 Steve White 16-MAR-71 800-555-1213
4 Gail Black 800-555-1214
5 Doreen Blue 20-MAY-70

The next query adds a WHERE clause to the previous example to just get customer #1 and #2:

-- GOOD (uses a WHERE clause to limit the rows retrieved)
SELECT *
FROM customers
WHERE customer_id IN (1, 2);
CUSTOMER_ID FIRST_NAME LAST_NAME DOB PHONE
----------- ---------- ---------- --------- ------------
1 John Brown 01-JAN-65 800-555-1211
2 Cynthia Green 05-FEB-68 800-555-1212

You should avoid using functions in the WHERE clause, as that increases execution time.

Use Table Joins Rather than Multiple Queries

If you need information from multiple related tables, you should use join conditions rather than multiple queries. In the following bad example, two queries are used to get the product name and the product type name for product #1 (using two queries is wasteful). The first query gets the name and product_type_id column values from the products table for product #1. The second query then uses that product_type_id to get the name column from the product_types table.

-- BAD (two separate queries when one would work)
SELECT name, product_type_id
FROM products
WHERE product_id = 1; 

NAME PRODUCT_TYPE_ID
------------------------------ ---------------
Modern Science 1


SELECT name
FROM product_types
WHERE product_type_id = 1; 

NAME
----------
Book


Instead of using the two queries, you should write one query that uses a join between the
products and product_types tables. The following good query shows this:


-- GOOD (one query with a join)>
SELECT p.name, pt.name
FROM products p, product_types pt
WHERE p.product_type_id = pt.product_type_id
AND p.product_id = 1; 

NAME NAME
------------------------------ ----------
Modern Science Book 

This query results in the same product name and product type name being retrieved as in the
first example, but the results are obtained using one query. One query is generally more efficient
than two.

You should choose the join order in your query so that you join fewer rows to tables later in
the join order. For example, say you were joining three related tables named tab1, tab2, and
tab3. Assume tab1 contains 1,000 rows, tab2 100 rows, and tab3 10 rows. You should join
tab1 with tab2 first, followed by tab2 and tab3.

Also, avoid joining complex views in your queries, because doing so causes the queries for
the views to be run first, followed by your actual query. Instead, write your query using the tables
rather than the views.

Continue reading this chapter by downloading a free .pdf of SQL Tuning from Oracle Database 11g SQL.    
						
						
						
				
						

							

						
								



 



 

















 
















	
	







	 


	



	

		

			Rate this Tip
		
	
	

		
		

		
		 To rate tips, you must be a member of SearchOracle.com.
Register now 
			to start rating these tips. Log in if you are already a member.
		
	
	
	
		 

 

									
									





	   

 



 


	
	
		 
		  
		  


	



	
	
	 

	
		

			

			
	
	
	 
	
		 
		 
		         

Digg This!
		   
      
		  StumbleUpon
		 
     

 Del.icio.us

      			 
 			
				
			
	 
			
		

 
	 
	 




	
	 
	
	
	

	

	




	

	
	    	
	 
	  
			        
				  
				     
	
	
	
		
		
			
			
				
					
		

			

				

				

				

		
				

				
RELATED CONTENT

				
				
				 
				 
					
					
					
						
							
							        
						
							  
								

	
										  
										

											

											
Chapter Downloads
										
										 
									
									
										     	
											   
											 
													

														

														
Oracle 11g: PL/SQL Basics
														
													
												  
									
										     	
											   
											 
													

														

														
Oracle 11g: Backup and recovery concepts
														
													
												  
									
										     	
											   
											 
													

														

														
Migrating to Oracle: Expert Secrets to Migrate from SQL Server and MySQL
														
													
												  
									
										       
											 
													

														

														
Upgrading to Oracle Database 11g
														
													
												  
									
										       
											 
													

														

														
Tuning the Oracle database with initialization parameters
														
													
												  
									
										     	
											   
											 
													

														

														
Protecting your online Oracle data
														
													
												  
									
										       
											 
													

														

														
Rapid application development of Oracle Web systems
														
													
												  
									
										     	
											   
											 
													

														

														
Oracle instance tuning techniques
														
													
												  
									
										     	
											   
											 
													

														

														
Oracle RAC performance tuning
														
													
												  
									
										       
											 
													

														

														
High-availability architecture and clusters
														
													
												    				
								 
								 
								    
			
		
		     
		
		
		
			
 

		

			

				

				

				

		
				

				
RELATED RESOURCES


						

						
2020software.com, trial software downloads for 
						accounting software, ERP software, CRM software and business software systems
						
					
						
Search Bitpipe.com for 
						the latest white papers and 
						business webcasts

						

						Whatis.com, the online computer dictionary
					
				
			
		
 
		   
									

									DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.