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
3
votes
0 answers

Logloss of binary classifier with constant prediction

What is the logloss of a binary classifier if we choose a constant prediction f=0.5? Is this development of the equation correct? logloss = (1/N) * sum(-y*log(f)-(1-y)*log(1-f)) logloss(y=0) = -(1-0)*log(1-f) = -log(f) logloss(y=1) = -1*log(f) =…
Seguy
  • 368
  • 2
  • 12
3
votes
1 answer

MySql Select - row subtract previous row

I'm trying to extract stats from DB. Table's structure is: UpdatedId product_name revenue 980 Product1 1000 975 Product1 950 973 Product1 900 970 Product1 800 965 Product21 1200 So…
Geobo
  • 159
  • 1
  • 1
  • 10
3
votes
2 answers

Why optimizer plan doesn't correlate with experimental query runs?

Suppose we have the following problem: Given a table with one column 'X', containing some rows with random integers from 1 to 100: CREATE TABLE xtable(x) AS SELECT ceil(dbms_random.value * 100) FROM dual CONNECT BY level <=…
diziaq
  • 6,881
  • 16
  • 54
  • 96
3
votes
0 answers

List of error/loss/cost functions in C#

I couldn't find a list in code and this seems like the right place for it. for a list of activation function look here. starting with the translation of the functions in this link. here is my attempt: (same here not sure if all the math is correct…
Rottjung
  • 493
  • 1
  • 5
  • 15
2
votes
0 answers

Finding optimal path

I am trying to find the optimal path for a car from given initial_state(of the form list[row,col,orientation]) to a given goal(of the form list[row,col]). The car is free to move forward in directions (up, left, down, right). It can perform 3…
Yash Raj
  • 21
  • 5
2
votes
1 answer

Optimizing over two loss functions in difrent ranges.

I am optimizing over two loss functions which take very different values. To give an example: loss1 = 1534 loss2 = 0.723 and I want to optimize over loss1+loss2. Would rescaling loss1 to values closer to loss2 be a good idea? I tried the naive way…
Qubix
  • 4,161
  • 7
  • 36
  • 73
2
votes
2 answers

Tensorflow cost function

I have an input dataset x with shape (10,1000), 10 inputs and 1000 lines and a output y with (1,1000), 1 output and 1000 lines. The cost function I defined is cost = tf.square(Y - prediction, name="cost") The prediction is a single predicted…
Yingchao Xiong
  • 255
  • 1
  • 3
  • 10
2
votes
0 answers

What is the estimate elapsed time for a query given its explained cost

If I use postgres's EXPLAIN command, there's a top-level "cost". Assuming that the explain is accurate (ie despite the cost being in reality quite unreliable and/or inconsistent), what is the very approximate conversion from cost to minutes/seconds…
Bohemian
  • 412,405
  • 93
  • 575
  • 722
2
votes
1 answer

SQLite: getting query cost from the query optimizer

For SQLite, given a SQL query Q, I am trying to figure out how to get the estimated query execution cost for Q out of the SQLite's query optimizer using C++ API. I've searched for this problem, found lots of discussions about this cost on SQLite's…
Adam Lee
  • 21
  • 2
2
votes
3 answers

PHP: return length of each array Element or stop

given I have an array, say: $myArray=['12','AB','3C'] I want to return the value 2 (which is the length of each of the array elements indivudually.) But in case I have something like $myArray=['12','AB2','3C'] I want to stop the calculation/loop…
Confidence
  • 2,283
  • 3
  • 33
  • 57
2
votes
2 answers

Convincing the Oracle SQL optimizer that an indexed (though non-UNIQUE) column actually contains unique values in practice

I am writing a view that uses a column with a non-UNIQUE index on it. However, within the context of my view, I am confident that the column will only contain unique values (due to the conditions imposed in the WHERE clause). The real problem…
Adam Paynter
  • 46,244
  • 33
  • 149
  • 164
1
vote
0 answers

How do you inspect candidate logical plans of cost-based SQL optimizer in spark (scala)?

For a project, I want to find a way to select the top-K resolved logical plans given a SQL query in spark, based on a cost-based optimizer. Is anyone aware of a spark SQL cost-based optimizer that computes some candidate plans where I could choose…
wpunter
  • 11
  • 1
1
vote
1 answer

Oracle Hash Join - Probe Table: Index over Partition?

Both Table P (Parent) and C (Child) have 10 partitions on cat and 316 subpartitions on effective_date. Table P has the following index create index ix_p_cat on p (cat);. How is it possible that an index range scan with an index on the partition…
Alex Bartsmon
  • 471
  • 4
  • 9
1
vote
2 answers

Oracle Avoid Wasteful Join Back?

Suppose we have three tables (A, B, and C) as defined in the contrived example below where A and B are related to (have foreign keys in) C. Suppose, we want values from all three tables and predicate on A and B. Oracle can only join two rowsets…
Alex Bartsmon
  • 471
  • 4
  • 9
1
vote
1 answer

what's the difference of 'not in' and 'not exists'?

sql1: select * from t1 where not exists (select a from t2 where t2.a = t1.a); sql2: select * from t1 where t1.a not in (select a from t2 where t2.a is not null); I think the sql1 is same as sql2, they will rewrite to anti join, right?
egraldlo
  • 183
  • 2
  • 11
1
2
3 4 5