Question

With the help of an example SQL query, explain the different steps that are carried out when cost...

With the help of an example SQL query, explain the different steps that are carried out when cost- based optimization is used by the optimization engine. Cleary state any assumption that you make.

0 0
Add a comment Improve this question Transcribed image text
Answer #1

The cost based optimisation actually determines the best execution plan among the others by considering available access path and also by factoring the information available in terms of statistics done on the schema object that are being accessed by the SQL statements. The cost based optimisation is used to set a potential plan which can be based on the hints and the available path for the sql query. Moreover, the optimizer is effective in predicting the cost of each plan that are based on the statistics done from the data dictionary for data distribution and also on the storage characteristics of the table.

The cost based optimizer also helps the SQL statement for better performance of the query. By using the below line we can optimize the sql statement :

OPTIMIZER_MODE = FIRST_ROWS_10

this will help in the query for faster response.

Add a comment
Know the answer?
Add Answer to:
With the help of an example SQL query, explain the different steps that are carried out when cost...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • With the help of an example SQL query, explain the different steps that are carried out...

    With the help of an example SQL query, explain the different steps that are carried out when cost- based optimization is used by the optimization engine. Cleary state any assumption that you make.

  • Q2. a) Explain the penetration test carried out on bitumen, state its significance and draw the...

    Q2. a) Explain the penetration test carried out on bitumen, state its significance and draw the test apparatus. b). Mention the main factors considered in the choice of construction materials at the design office – is any of them more important than the other? c). What factors affect the weldability of steel? d). Consider the iron value chain from material extraction to disposal, at what point according to your assessment is the most energy used? As a design engineer, at...

  • **************PLEASE COMPLETE PART F) ONLY************** Consider the following relational database schema (primary keys are underlined) and...

    **************PLEASE COMPLETE PART F) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...

  • **************PLEASE COMPLETE PART E) ONLY************** Consider the following relational database schema (primary keys are underlined) and...

    **************PLEASE COMPLETE PART E) ONLY************** Consider the following relational database schema (primary keys are underlined) and SQL query: Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress) SELECT g.guestNo, g.guestName FROM Room r, Booking b, Hotel h, Guest g WHERE h.hotelNo = b.hotelNo AND g.guestNo = b.guestNo AND h.hotelNo = r.hotelNo AND h.hotelName = "Ritz" AND dateFrom >= "Jan 01, 2001" AND dateTo <= "Dec 31, 2001"; (A) state what...

  • Cost Behavior Major Concept Assmt #1 Explain and give a unique example the following concepts of...

    Cost Behavior Major Concept Assmt #1 Explain and give a unique example the following concepts of cost behaviors: • Cost drivers Fixed cost and Variable Costs • CVP Analysis Write in complete sentences and pay close attention to grammar Please make sure to clearly explain the concepts as if you were teaching and explaining to another student, that has no background on these concepts. Also, make sure to use a unique example. Take this example from your own situation, either...

  • Cost Behavior Major Concept Assmt #1 Explain and give a unique example the following concepts of...

    Cost Behavior Major Concept Assmt #1 Explain and give a unique example the following concepts of cost behaviors: • Cost drivers • Fixed cost and Variable Costs • CVP Analysis Write in complete sentences and pay close attention to grammar Please make sure to clearly explain the concepts as if you were teaching and explaining to another student, that has no background on these concepts. Also, make sure to use a unique example. Take this example from your own situation,...

  • QUESTION: (Answer in SQL) Write an SQL query to find all pairs of users (A,B) such...

    QUESTION: (Answer in SQL) Write an SQL query to find all pairs of users (A,B) such that both A and B have blurted on a common topic but A is not following B. Your query should print the names of A and B in that order. BACKGROUND INFO: Users can post their thoughts in form of short messages that we call “blurts”. When signing up, users need to provide their email and a password of their choice. In addition, they...

  • a.From the table below, Use sql query to give the total cost of each Ice cream...

    a.From the table below, Use sql query to give the total cost of each Ice cream flavor b. give the count of customers that like exactly two flavors CUSTOMER (Cust_ID, Cust_name, year_born) They want to link CUSTOMER to their database. Customer flavor preference is shown in table 2. Following tables are from assignment 1 ICECREAM (Ice_cream_ID, Ice_cream_flavor, price, years_first_offered, sellling _status) INGREDIENT( Ingredient_ID, Ingredient_name, cost) RECIPE (Ice_cream_ID, ingredient_ID, quantity_used) WHERE: Ice_cream_ID is the internal Id given to an ice cream....

  • Project Steps: 1. Database Design 1.1. Design and create a database in third normal form based...

    Project Steps: 1. Database Design 1.1. Design and create a database in third normal form based on the following requirements: • Each Job is for a specific customer and there can be more than one job per customer. • The name and address must be tracked for each customer. • Each job is described by up to 2000 characters. • Each job has a status of ‘open, ‘in process’, or ‘complete’. • Each job has a start date and end...

  • 2. When a neutralization reaction was carried out using 100.0 ml of 0.7890M NH3 water and...

    2. When a neutralization reaction was carried out using 100.0 ml of 0.7890M NH3 water and 100.0 ml of 0.7940M acetic acid, AT was found to be 4.76 "C The specific heat of the reaction mixture was 4.104 -1K-1 and its density was 1.03 g ml 1. The calorimeter constant was 3.361 K-1 1) Calculate AH neutan for the reaction of NH3 and acetic acid 06) At the end of the experiment, it was discovered that the thermometer had not...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT