Stack Overflow
Stack Overflow

Questions tagged [cost-based-optimizer]

CBO or Cost-Based-Optimization is a collection of techniques within a RDBMS designed to evaluate numerically how expensive a SQL statement will be. Any SQL statement can be executed in different ways. Each of these ways is a possible execution plan. The CBO will assign to each plan a cost which represents a numeric evaluation of how expensive the operation will be. It will always select the plan with the lowest cost. Lowest cost in this regard logically represents the lowest elapsed time to execute such query.

For a CBO to work properly, statistics are necessary and should be maintained regularly. Oracle , Postgres, MySQL, etc are examples of RDBMS which use the CBO method to evaluate the best way to execute SQL statements.

Use this tag for questions related to execution plans of queries based on the CBO method.

75 questions
162
votes
10 answers

PostgreSQL - fetch the rows which have the Max value for a column in each GROUP BY group

I'm dealing with a Postgres table (called "lives") that contains records with columns for time_stamp, usr_id, transaction_id, and lives_remaining. I need a query that will give me the most recent lives_remaining total for each usr_id There are…
Joshua Berry
  • 2,230
  • 3
  • 21
  • 24
12
votes
2 answers

Do foreign key constraints influence query transformations in Oracle?

I have a situation like this: create table a( a_id number(38) not null, constraint pk_a primary key (id) ); create table b( a_id number(38) not null ); create index b_a_id_index on b(a_id); Now b.a_id is in fact meant to be a foreign key…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
10
votes
2 answers

sum over a list of tensors in tensorflow

I have a deep neural network where the weights between layers are stored in a list. layers[j].weights I want to incluse the ridge penalty in my cost function. I need then to use something like tf.nn.l2_loss(layers[j].weights**2 for j in…
Donbeo
  • 17,067
  • 37
  • 114
  • 188
9
votes
5 answers

Join elimination not working in Oracle with sub queries

I am able to get join elimination to work for simple cases such as one-to-one relations, but not for slightly more complicated scenarios. Ultimately I want to try anchor modelling, but first I need to find a way around this problem. I'm using Oracle…
Ronnis
  • 12,593
  • 2
  • 32
  • 52
9
votes
6 answers

Optimizing cartesian requests with affine costs

I have a cost optimization request that I don't know how if there is literature on. It is a bit hard to explain, so I apologize in advance for the length of the question. There is a server I am accessing that works this way: a request is made on…
LeMiz
  • 5,554
  • 5
  • 28
  • 23
6
votes
1 answer

When does the Oracle CBO choose to execute a "merge join cartesian" operation?

From time to time, Oracle seems to prefer a MERGE JOIN CARTESIAN operation over a regular MERGE JOIN. Knowing the data and looking at concrete execution plans, I can see that this operation is usually not a problem, as one of the joined entities can…
Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
4
votes
1 answer

how to write "then" as IP constraint in Julia

Hello fellows, i am learning Julia and integer programing but i am stuck at one point How to model "then" in julia-jump for integer programing leanring. Stuck here here #Define the variables of the model @variable(mo, x[1:N,1:S],…
sana ullah
  • 43
  • 3
4
votes
3 answers

Why are bad row estimates slow in Postgres?

What makes bad row estimates a pain point in SQL query performance? I’m interested to know the internal reasons why. Often a bad row estimate will actually pick the correct plan, and the only difference between a good query and a bad query will be…
Rol
  • 501
  • 4
  • 13
4
votes
1 answer

Estimator in Oracle

In Oracle's documentation, for the estimator in optimizer, there is a schema like this: https://docs.oracle.com/database/121/TGSQL/img/GUID-22630970-B584-41C9-B104-200CEA2F4707-default.gif Normally, as I know, the plan generator generates the plans…
oramas
  • 881
  • 7
  • 12
4
votes
1 answer

Why aren't neural networks convex?

Unlike linear and logistic regression, ANNs cost functions are not convex, and thus are susceptible to local optima. Can anyone provide an intuition as to why this is the case for ANNs and why the hypothesis cannot be modified to produce a convex…
Free Url
  • 1,836
  • 2
  • 15
  • 28
4
votes
1 answer

Applying Cost Functions in R

I am in the beginning stages of machine learning in R and I find it hard to believe that there are no packages to solving the cost function for different types of regression algorithms. For example, if I want to solve the cost function for a…
nak5120
  • 4,089
  • 4
  • 35
  • 94
4
votes
0 answers

Methods for optimizing short-circuit evaluation for conditions of varying evaluation-cost

This is a bit of an abstract question, I hope that's ok (if not, please let me know of a better place to ask it): I have a bunch of boolean conditions, let's call them A, B, C, D, .... In my code, I need to use these conditions to distinguish…
Markus A.
  • 12,349
  • 8
  • 52
  • 116
4
votes
1 answer

Reasons for Oracle Automatic Degree of Parallelism?

I apologise in advance as I am new to this area and that I may not be providing all the required information up front but here goes. I am on a two node Oracle RAC. Optimizer stats have been recently collected and are very accurate. I have a query…
Kyle
  • 55
  • 4
3
votes
1 answer

How to optimize the electric vehicle charging cost using Gekko?

from gekko import GEKKO import numpy as np import matplotlib.pyplot as plt m = GEKKO() m.options.SOLVER = 1 m.options.IMODE = 3 Num_car = 1 TOU =…
LAP1040400
  • 31
  • 3
3
votes
0 answers

Spark CBO not showing rowcount for queries having partition column in query

I'm working on Spark 2.3.0 using Cost Based Optimizer(CBO) for computing statistics for queries on done on external tables. I have a created a external table in spark : CREATE EXTERNAL TABLE IF NOT EXISTS test ( eventID string,type string,exchange…
Rajat Mishra
  • 3,635
  • 4
  • 27
  • 41
1
2 3 4 5