Posted to tcl by Stu at Thu Sep 08 18:12:22 GMT 2022view raw

  1. if 0 {
  2. -- Suppose a table: n char(30), v char(1).
  3. CREATE TABLE t(n char(30), v char(1));
  4.  
  5. -- Create the table with appropriate indexes then fill 300,000 recs with
  6. -- n=random 30 chars and v=random chars in range [A-Z].
  7. INSERT INTO t(n, v)
  8. WITH RECURSIVE tmp(n, v) AS (
  9. SELECT 1, SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', FLOOR((RANDOM() + 9223372036854775808) / 2.0 / 9223372036854775808 * 26) + 1, 1)
  10. UNION ALL
  11. SELECT n + 1, SUBSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ', FLOOR((RANDOM() + 9223372036854775808) / 2.0 / 9223372036854775808 * 26) + 1, 1)
  12. FROM tmp
  13. LIMIT 300000 -- !!! Replace with the actual number of records !!!
  14. )
  15. SELECT (
  16. WITH rand_string(n, v, chars) AS (
  17. SELECT 1, '', 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
  18. UNION ALL
  19. SELECT rand_string.n + 1, rand_string.v || SUBSTR(chars, FLOOR(( (0 * tmp.n) + RANDOM() + 9223372036854775808) / 2.0 / 9223372036854775808 * LENGTH(chars)) + 1, 1), chars
  20. FROM rand_string
  21. WHERE rand_string.n <= 30
  22. )
  23. SELECT rand_string.v
  24. FROM rand_string
  25. WHERE rand_string.n = 30
  26. ), tmp.v
  27. FROM tmp;
  28.  
  29. CREATE INDEX tvn ON t(v, n);
  30.  
  31. ANALYZE t;
  32. }