(转载)98-老虎刘和chatGPT pk了一把SQL改写优化

newkid

below is a PL/SQL code block, can you optimize it?

declare
  v number;
begin
  v :=0 ;
  for i in 1.1000*1000*1000 loop
     v := v+1 ;
     v := v-1 ;
  end loop;
end;



The PL/SQL code block you provided seems to be an example of a loop that performs no meaningful operation. It simply increments and decrements the value of a variable within a loop that iterates 1.1 billion times. It is not clear what the purpose of this code is, but it appears to be a performance test.

To optimize this code, you can replace the loop with a single assignment statement that sets the value of v to zero, since the loop is effectively doing nothing. This will eliminate the need for the loop and significantly improve performance:

DECLARE
  v NUMBER := 0;
BEGIN
  v := 0;
END;

Alternatively, if you need to perform some meaningful operation in the loop, you should update the loop body accordingly to achieve the desired outcome. Additionally, you should consider breaking the loop into smaller chunks to improve performance, especially if you are dealing with a large number of iterations.

newkid

Can you optimize this PL/SQL block:

declare
  v number;
begin
  v :=0 ;
  for i in 1..1000*1000*1000 loop
     v := v+1 ;
  end loop;
end;

The PL/SQL code block you provided increments a variable v by 1 for 1 billion times using a loop. This can be optimized by using a single assignment statement to set the value of v to 1 billion directly, instead of incrementing it in a loop.

DECLARE
  v NUMBER := 1000000000;
BEGIN
  NULL; -- do nothing
END;

This code block initializes the variable v with the value of 1 billion directly, without the need for a loop. The NULL statement is used as a placeholder in the BEGIN block since no other operations are performed in this code. This will significantly improve the performance of your code.

newkid

Can you optimize this PL/SQL block:

declare
  v number;
begin
  v :=0 ;
  for i in 1..1000*1000*1000 loop
     if i<>500 then
        v:=v+1 ;
     end if;
  end loop;
end;

The PL/SQL code block you provided increments a variable v by 1 for 1 billion times using a loop, but skips the increment when the loop index i is equal to 500. This can be optimized by calculating the value of v directly, without the loop.

DECLARE
  v NUMBER := 999999999;
BEGIN
  NULL; -- do nothing
END;
This code block initializes the variable v with the value of 999,999,999, which is the expected value of v after skipping the increment at index 500. The NULL statement is used as a placeholder in the BEGIN block since no other operations are performed in this code. This will significantly improve the performance of your code.

If you need to perform a more complex operation inside the loop, you can still optimize the code by reducing the number of iterations. For example, if you only need to skip the increment at index 500, you can modify the loop to iterate from 1 to 499 and from 501 to 1000000000, skipping the increment only for index 500:

DECLARE
  v NUMBER := 0;
BEGIN
  for i in 1..499 loop
    v := v + 1;
  end loop;
  
  for i in 501..1000000000 loop
    v := v + 1;
  end loop;
END;

This code will achieve the same result as the original code, but with fewer iterations.

jihuyao

newkid 发表于 2023-3-21 22:27
Can you optimize this PL/SQL block:declare  v number;begin  v :=0 ;  for i in 1..1000*1000*1000 loop ...

Ha, very nice!  How about perform a nested loop with some easy traps?

jihuyao

I am thinking about the statistical approach for grouping problem with a specific pattern.  Given 3(3), 5(4), 3(6), 5(8), 2(9), 5(10), m(i), m is the count of a value n in given value collections.  I just want to take a sample of values that , I know, has perfect grouping result and to use it to test the statistical approach.
Now it would be intersting to see how chatGPT handles this simple data distribution and operates as human to distribute them into 5 groups.

newkid

jihuyao 发表于 2023-3-25 10:31
I am thinking about the statistical approach for grouping problem with a specific pattern.  Given 3( ...

把你的程序贴出来，再让它优化看看。

jihuyao

newkid 发表于 2023-3-26 02:44
把你的程序贴出来，再让它优化看看。

Just want to see if chapGPT has a quick human sight for given collection

3(3), 5(4), 3(6), 5(8), 2(9), 5(10)

and quickly put 3*3+3*6=3*9 and then 2*9+3*9=5*9 (presented by 5(9)).  Therefore the perfect grouping is

4+8+10+9 (for each group).

Is chapGPT really able to see it quickly and give the answer without running any program?

jihuyao

Here is a short one with nested loop and see if chapGPT can see the trick in it and re-write it using the formula below.
declare

n number :=&n ;
v number :=0 ;
begin
for i in 1..n loop
for j in i..n-i loop
v:=v+1 ;
end loop ;
end loop ;
DBMS_OUTPUT.PUT_LINE( 'v='||v ) ;
end ;
/
SQL> /
Enter value for n: 100
old   2: n number :=&n ;
new   2: n number :=100 ;
v=2500

PL/SQL procedure successfully completed.

SQL> /
Enter value for n: 1000
old   2: n number :=&n ;
new   2: n number :=1000 ;
v=250000

--==========================================================
the result from the nested loop can be obtained from below formula,

declare
n number :=&n ;
v number :=0 ;
begin
v:= (1+n-1)*(n/2)/2 ;
DBMS_OUTPUT.PUT_LINE( 'v='||v) ;
end ;
/




Wrote file afiedt.buf

  1  declare
  2  n number :=&n ;
  3  v number :=0 ;
  4  begin
  5  v:= (1+n-1)*(n/2)/2 ;
  6  DBMS_OUTPUT.PUT_LINE( 'v='||v) ;
  7* end ;
SQL> /
Enter value for n: 100
old   2: n number :=&n ;
new   2: n number :=100 ;
v=2500

PL/SQL procedure successfully completed.

SQL> /
Enter value for n: 1000
old   2: n number :=&n ;
new   2: n number :=1000 ;
v=250000

PL/SQL procedure successfully completed.

jihuyao

I am also wondering if chatGPT can see some simple hidden rules/patterns, like two shown below (one for number and one for string.

1.  given a function f, f(7)=8 and f(6)=5.  f(11)+f(9)=?

2.  given a function f, s is string, f(s1)=2 and f(s2)=1. here s1= ( (a+f)+( (b+c)+(d+e) ) ) and s2=(a+(b+c+d+e)).

f(s3)+f(s4)=?

here s3=(a+(b+c)+(d+(e+f)))  and s4=((a+b)+(c+e)+(f+g))

Does #2 needs some hint?  All given strings s1 to s4 can be considered as containing nested () in different levels, eg, besides level 0 (root level), from outside to inside having 0 or more level 1 (), 0 or more level 2, and so on.

If your guys think something not clearly stated please point out.  But I am afraid it still underestimates the ability of chatGPT.

jihuyao

I am also wondering if chatGPT can see some simple hidden rules/patterns, like two shown below (one for number and one for string.

1.  given a function f, f(7)=8 and f(6)=5.  f(11)+f(9)=?

2.  given a function f, s is string, f(s1)=2 and f(s2)=1. here s1= ( (a+f)+( (b+c)+(d+e) ) ) and s2=(a+(b+c+d+e)).

f(s3)+f(s4)=?

here s3=(a+(b+c)+(d+(e+f)))  and s4=((a+b)+(c+e)+(f+g))

Does #2 needs some hint?  All given strings s1 to s4 can be considered as containing nested () in different levels, eg, besides level 0 (root level), from outside to inside having 0 or more level 1 (), 0 or more level 2, and so on.

If your guys think something not clearly stated please point out.  But I am afraid it still underestimates the ability of chatGPT.