14 Replies Latest reply on Jul 2, 2013 9:18 PM by BrendanP

    Processing Cost - How to catch a soccer team with the highest combined score?

    Filippe Soares Roza

      Hi to all.

       

      It's a pleasure to receive your help.


      I am using Oracle Database 11g Express Edition Release 11.2.0.2.0 - Beta and I am working with the two follows tables.

      I want to get a soccer team with the highest combined score averages and participations considering all possible formations and my budget, but I'm not finding a way to get a cost effective processing.


      Suggestions?

       

      create table ESQUEMA
      (
        ESQUEMA   INTEGER,
        ZAGUEIROS INTEGER,
        LATERAIS  INTEGER,
        MEIAS     INTEGER,
        ATACANTES INTEGER,
        P1        CHAR(3),
        P2        CHAR(3),
        P3        CHAR(3),
        P4        CHAR(3),
        P5        CHAR(3),
        P6        CHAR(3),
        P7        CHAR(3),
        P8        CHAR(3),
        P9        CHAR(3),
        P10       CHAR(3)
      )
      ;
      
      
      

       

      esquema table - sample data

      ESQUEMAZAGUEIROSLATERAISMEIASATACANTESP1P2P3P4P5P6P7P8P9P10
      14422242ZAGZAGLATLATMEIMEIMEIMEIATAATA
      24332233ZAGZAGLATLATMEIMEIMEIATAATAATA
      35323232ZAGZAGZAGLATLATMEIMEIMEIATAATA
      4352352ZAGZAGZAGMEIMEIMEIMEIMEIATAATA
      5343343ZAGZAGZAGMEIMEIMEIMEIATAATAATA
      64512251ZAGZAGLATLATMEIMEIMEIMEIMEIATA
      75413241ZAGZAGZAGLATLATMEIMEIMEIMEIATA


      create table JOGADORES
      (
        ID      INTEGER not null,
        CLUBE   VARCHAR2(100),
        NOME    VARCHAR2(100),
        POSICAO CHAR(3),
        PRECO   NUMBER(4,2),
        MEDIA   NUMBER(4,2),
        JOGOS   INTEGER
      )
      ;
      
      
      


      jogadores table - sample data

      IDCLUBENOMEPOSICAOPRECOMEDIAJOGOS
      80403FluminenseAbel BragaTEC17,515,364
      70393CoritibaAlexMEI16,985,085
      42365NáuticoAuremirLAT7,735,484
      18341GrêmioBarcosATA18,963,674
      102425GrêmioBressanZAG10,855,904
      31354CrisciúmaBrunoGOL10,663,205
      105428CruzeiroBruno RodrigoZAG15,475,285
      82405VitóriaCaio JúniorTEC11,404,455
      41364FluminenseCarlinhosLAT12,406,933
      61384VascoCarlos AlbertoMEI15,016,752
      13336Ponte PretaChiquinhoATA9,974,645
      72395Ponte PretaCicinhoMEI11,424,725
      92415SantosClaudinei OliveiraTEC11,923,173
      104427Ponte PretaCléberZAG14,615,785
      68391PortuguesaCorreaMEI8,445,604
      93416BahiaCristóvão BorgesTEC8,272,925
      96419Atlético-MGCucaTEC12,622,324
      29352CorinthiansCássioGOL12,513,745


      I want a result like this query could bring, if it had a low cost. Should I make any changes in tables or attempting to use PL / SQL?

      select *
        from jogadores j1,
             jogadores j2,
             jogadores j3,
             jogadores j4,
             jogadores j5,
             jogadores j6,
             jogadores j7,
             jogadores j8,
             jogadores j9,
             jogadores j10,
             jogadores j11,
             jogadores j12,
             esquema   e
      where j1.posicao = e.p1
         and j2.posicao = e.p2
         and j3.posicao = e.p3
         and j4.posicao = e.p4
         and j5.posicao = e.p5
         and j6.posicao = e.p6
         and j7.posicao = e.p7
         and j8.posicao = e.p8
         and j9.posicao = e.p9
         and j10.posicao = e.p10
         and j11.posicao = 'TEC'
         and j12.posicao = 'GOL'
            -- 
         and j1.id NOT IN (j2.id,
                           j3.id,
                           j4.id,
                           j5.id,
                           j6.id,
                           j7.id,
                           j8.id,
                           j9.id,
                           j10.id,
                           j11.id,
                           j12.id)
            --
         and j2.id NOT IN (j3.id,
                           j4.id,
                           j5.id,
                           j6.id,
                           j7.id,
                           j8.id,
                           j9.id,
                           j10.id,
                           j11.id,
                           j12.id)
            --
         and j3.id NOT IN
             (j4.id, j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
            --
         and j4.id NOT IN
             (j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
            --
         and j5.id NOT IN (j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
            --
         and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
            --
         and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)
            --
         and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)
            --
         and j9.id NOT IN (j10.id, j11.id, j12.id)
            --
         and j10.id NOT IN (j11.id, j12.id)
            --
         and j11.id <> j12.id
         and (e.esquema,
              (j1.media + j2.media + j3.media + j4.media + j5.media + j6.media +
              j7.media + j8.media + j9.media + j10.media + j11.media + j12.media +
              j1.jogos + j2.jogos + j3.jogos + j4.jogos + j5.jogos + j6.jogos +
              j7.jogos + j8.jogos + j9.jogos + j10.jogos + j11.jogos + j12.jogos)) in
             (select e.esquema,
                     nvl(max(j1.media + j2.media + j3.media + j4.media + j5.media +
                             j6.media + j7.media + j8.media + j9.media + j10.media +
                             j11.media + j12.media + j1.jogos + j2.jogos +
                             j3.jogos + j4.jogos + j5.jogos + j6.jogos + j7.jogos +
                             j8.jogos + j9.jogos + j10.jogos + j11.jogos +
                             j12.jogos),
                         0)
                from jogadores j1,
                     jogadores j2,
                     jogadores j3,
                     jogadores j4,
                     jogadores j5,
                     jogadores j6,
                     jogadores j7,
                     jogadores j8,
                     jogadores j9,
                     jogadores j10,
                     jogadores j11,
                     jogadores j12,
                     esquema   e
               where j1.posicao = e.p1
                 and j2.posicao = e.p2
                 and j3.posicao = e.p3
                 and j4.posicao = e.p4
                 and j5.posicao = e.p5
                 and j6.posicao = e.p6
                 and j7.posicao = e.p7
                 and j8.posicao = e.p8
                 and j9.posicao = e.p9
                 and j10.posicao = e.p10
                 and j11.posicao = 'TEC'
                 and j12.posicao = 'GOL'
                    --      
                 and j1.id NOT IN (j2.id,
                                   j3.id,
                                   j4.id,
                                   j5.id,
                                   j6.id,
                                   j7.id,
                                   j8.id,
                                   j9.id,
                                   j10.id,
                                   j11.id,
                                   j12.id)
                    --
                 and j2.id NOT IN (j3.id,
                                   j4.id,
                                   j5.id,
                                   j6.id,
                                   j7.id,
                                   j8.id,
                                   j9.id,
                                   j10.id,
                                   j11.id,
                                   j12.id)
                    --
                 and j3.id NOT IN (j4.id,
                                   j5.id,
                                   j6.id,
                                   j7.id,
                                   j8.id,
                                   j9.id,
                                   j10.id,
                                   j11.id,
                                   j12.id)
                    --
                 and j4.id NOT IN
                     (j5.id, j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
                    --
                 and j5.id NOT IN
                     (j6.id, j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
                    --
                 and j6.id NOT IN (j7.id, j8.id, j9.id, j10.id, j11.id, j12.id)
                    --
                 and j7.id NOT IN (j8.id, j9.id, j10.id, j11.id, j12.id)
                    --
                 and j8.id NOT IN (j9.id, j10.id, j11.id, j12.id)
                    --
                 and j9.id NOT IN (j10.id, j11.id, j12.id)
                    --
                 and j10.id NOT IN (j11.id, j12.id)
                    --
                 and j11.id <> j12.id
                 and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco +
                     j6.preco + j7.preco + j8.preco + j9.preco + j10.preco +
                     j11.preco + j12.preco) <= &patrimonio
               group by e.esquema)
         and (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco + j6.preco +
             j7.preco + j8.preco + j9.preco + j10.preco + j11.preco + j12.preco) <=
             &patrimonio;
      
      
      


      Thank in advance,

      Filippe

        • 1. Re: Processing Cost - How to catch a soccer team with the highest combined score?
          jjk

          Provide a sample required output with explanation and rules, if any (since there may be atleast few people like me who dont know anything about soccer ). That'll help better in formulating a query

          1 person found this helpful
          • 2. Re: Processing Cost - How to catch a soccer team with the highest combined score?
            BrendanP

            Message was edited by: BrendanP Forum has truncated this post, so I made another one using '&lt' and '&gt' - see the earlier post

            Also, put it on my blog, here

             

            Fantasy Football Team Selection in SQL

             

            Message was edited by: BrendanP

            • 3. Re: Processing Cost - How to catch a soccer team with the highest combined score?
              BrendanP

              Nice question, fantasy football - this is another knapsack problem, single container version. I've solved that on this forum before and here adapt the solution for this case. Thanks to OP for specifying in two languages to make it more interesting:)

               

              Note that I have taken OP's test data but anglicised the columns (no guarantee of language correctness though), and removed unnecessary columns, etc. You need a definite objective function, so I have just multiplied appearances by average points, but you can use any function of the fields. I have also arbitrarily taken a maximum price of 130.

               

              Performance is of course a key issue with combinatorial problems; hopefully, using the positional constraints early on helps with that.

              Input Formations
              SQL&gt SELECT *
                2    FROM formations
                3   ORDER BY 1
                4  /
              
              ID  CENTRE_BACKS WING_BACKS MIDFIELDERS   FORWARDS
              --- ------------ ---------- ----------- ----------
              343            3          0           4          3
              352            3          0           5          2
              433            2          2           3          3
              442            2          2           4          2
              451            2          2           5          1
              532            3          2           3          2
              541            3          2           4          1
              
              7 rows selected.
              
              SQL&gt COLUMN club_name FORMAT A15
              SQL&gt COLUMN player_name FORMAT A20
              SQL&gt PROMPT Input Players
              Input Players
              SQL&gt SELECT id,
                2         club_name,
                3         player_name,
                4         position,
                5         price,
                6         avg_points,
                7         appearances,
                8         avg_points * appearances tot_points,
                9         Round (avg_points * appearances / price, 2) utility,
               10         Rank() OVER (ORDER BY avg_points * appearances DESC) t_rank,
               11         Rank() OVER (ORDER BY price) p_rank
               12    FROM players
               13   ORDER BY avg_points * appearances / price DESC
               14  /
              
                      ID CLUB_NAME       PLAYER_NAME          PO      PRICE AVG_POINTS APPEARANCES TOT_POINTS    UTILITY     T_RANK     P_RANK
              ---------- --------------- -------------------- -- ---------- ---------- ----------- ---------- ---------- ---------- ----------
                       4 Náutico         Auremir              WB       7.73       5.48           4      21.92       2.84          9          1
                      14 Portuguesa      Correa               MF       8.44        5.6           4       22.4       2.65          7          3
                      17 Ponte Preta     Chiquinho            FW       9.97       4.64           5       23.2       2.33          6          4
                       1 Grêmio          Bressan              CB      10.85        5.9           4       23.6       2.18          4          6
                      12 Ponte Preta     Cicinho              MF      11.42       4.72           5       23.6       2.07          4          8
                       3 Ponte Preta     Cléber               CB      14.61       5.78           5       28.9       1.98          1         13
                      10 Vitória         Caio Júnior          MF       11.4       4.45           5      22.25       1.95          8          7
                      15 Bahia           Cristóvão Borges     MF       8.27       2.92           5       14.6       1.77         15          2
                       2 Cruzeiro        Bruno Rodrigo        CB      15.47       5.28           5       26.4       1.71          2         15
                       7 Fluminense      Carlinhos            WB       12.4       6.93           3      20.79       1.68         11         10
                       5 Crisciúma       Bruno                WB      10.66        3.2           5         16        1.5         13          5
                       9 Coritiba        Alex                 MF      16.98       5.08           5       25.4        1.5          3         16
                       6 Corinthians     Cássio               WB      12.51       3.74           5       18.7       1.49         12         11
                       8 Fluminense      Abel Braga           MF      17.51       5.36           4      21.44       1.22         10         17
                      11 Vasco           Carlos Alberto       MF      15.01       6.75           2       13.5         .9         16         14
                      13 Santos          Claudinei Oliveira   MF      11.92       3.17           3       9.51         .8         17          9
                      18 Grêmio          Barcos               FW      18.96       3.67           4      14.68        .77         14         18
                      16 Atlético-MG     Cuca                 MF      12.62       2.32           4       9.28        .74         18         12
              
              18 rows selected.
              
              SQL&gt PROMPT Top ten solutions
              Top ten solutions
              SQL&gt WITH rsf (nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
                2  SELECT p.id, 0 lev, p.price, p.avg_points * p.appearances,
                3         CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
                4         CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
                5         CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
                6         CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
                7         To_Char (p.id) path
                8    FROM players p
                9   WHERE EXISTS (SELECT 1 FROM formations WHERE
               10                     centre_backs &gt= CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
               11                     wing_backs   &gt= CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
               12                     midfielders  &gt= CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
               13                     forwards     &gt= CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
               14                )
               15   UNION ALL
               16  SELECT p.id,
               17         r.lev + 1,
               18         r.tot_price + p.price,
               19         r.tot_profit + p.avg_points * p.appearances,
               20         r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
               21         r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
               22         r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
               23         r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
               24         r.path || ',' || To_Char (p.id)
               25    FROM rsf r
               26    JOIN players p
               27      ON p.id &gt r.nxt_id
               28   WHERE EXISTS (SELECT 1 FROM formations WHERE
               29                     centre_backs &gt= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
               30                     wing_backs   &gt= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
               31                     midfielders  &gt= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
               32                     forwards     &gt= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
               33                )
               34    AND r.tot_price + p.price &lt= 130
               35  ) SEARCH DEPTH FIRST BY nxt_id SET line_no
               36  , paths_ranked AS (
               37  SELECT tot_price,
               38         tot_profit,
               39         Row_Number () OVER (ORDER BY tot_profit DESC) r_profit,
               40         path
               41    FROM rsf
               42  ), top_ten_paths AS (
               43  SELECT tot_price,
               44         tot_profit,
               45         r_profit,
               46         ',' || path || ',' path,
               47         player_index
               48    FROM paths_ranked
               49    CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL &lt 11)
               50   WHERE r_profit &lt= 10
               51  ), top_ten_teams AS (
               52  SELECT tot_price,
               53         tot_profit,
               54         r_profit,
               55         path,
               56         player_index,
               57         Substr (path,
               58                 Instr (path, ',', 1, player_index) + 1,
               59                 Instr (path, ',', 1, player_index + 1) - Instr (path, ',', 1, player_index) - 1) player_id
               60    FROM top_ten_paths
               61  )
               62  SELECT t.tot_profit,
               63         t.tot_price,
               64         t.r_profit rnk,
               65         p.position,
               66         t.player_id p_id,
               67         p.player_name,
               68         p.club_name,
               69         p.price,
               70         p.avg_points,
               71         p.appearances apps,
               72         p.avg_points * p.appearances profit
               73    FROM top_ten_teams t
               74    JOIN players p
               75      ON p.id = t.player_id
               76  ORDER BY t.r_profit, t.path, t.player_index
               77  /
              
              TOT_PROFIT  TOT_PRICE        RNK PO P_ID PLAYER_NAME          CLUB_NAME            PRICE AVG_POINTS       APPS     PROFIT
              ---------- ---------- ---------- -- ---- -------------------- --------------- ---------- ---------- ---------- ----------
                  238.46     119.27          1 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                  237.65     125.38          2 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                   237.5     128.34          3 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                  236.37     119.38          4 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  6    Cássio               Corinthians          12.51       3.74          5       18.7
                                               MF 9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                   236.3     125.36          5 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                             125.93          6 CB 2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                  235.56     125.49          7 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  6    Cássio               Corinthians          12.51       3.74          5       18.7
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                  235.41     128.45          8 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  6    Cássio               Corinthians          12.51       3.74          5       18.7
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                  235.24     124.05          9 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 6    Cássio               Corinthians          12.51       3.74          5       18.7
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 9    Alex                 Coritiba             16.98       5.08          5       25.4
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                   234.5      119.8         10 CB 1    Bressan              Grêmio               10.85        5.9          4       23.6
                                                  2    Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                  3    Cléber               Ponte Preta          14.61       5.78          5       28.9
                                               WB 4    Auremir              Náutico               7.73       5.48          4      21.92
                                                  7    Carlinhos            Fluminense            12.4       6.93          3      20.79
                                               MF 8    Abel Braga           Fluminense           17.51       5.36          4      21.44
                                                  10   Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                  12   Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                  14   Correa               Portuguesa            8.44        5.6          4       22.4
                                               FW 17   Chiquinho            Ponte Preta           9.97       4.64          5       23.2
              
              100 rows selected.
              
              Elapsed: 00:00:05.55
              1 person found this helpful
              • 4. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                Filippe Soares Roza

                Hi.

                 

                Note: coach (TEC) and goalkeeper (GOL) are standard players.

                 

                Seeking twelve players that add the greatest result: average points + number of participations. Therefore, considering all the formations. Which returns me the best result? For example: suppose we get the largest selection of summation and within my budget (I consider 200) with 442 formation. Follow simulation.

                 

                The largest sum was found: 134.71. The formation that allowed this sum was: 442.

                select '442' FORMATION,
                       (j1.media + j1.jogos + j2.media + j2.jogos + j3.media + j3.jogos +
                       j4.media + j4.jogos + j5.media + j5.jogos + j6.media + j6.jogos +
                       j7.media + j7.jogos + j8.media + j8.jogos + j9.media + j9.jogos +
                       j10.media + j10.jogos + j11.media + j11.jogos + j12.media +
                       j12.jogos) GREATEST_RESULT,
                       (j1.preco + j2.preco + j3.preco + j4.preco + j5.preco + j6.preco +
                       j7.preco + j8.preco + j9.preco + j10.preco + j11.preco + j12.preco) PRICE_TEAM,
                       j1.clube,
                       j1.nome,
                       j1.posicao,
                       j2.clube,
                       j2.nome,
                       j2.posicao,
                       j3.clube,
                       j3.nome,
                       j3.posicao,
                       j4.clube,
                       j4.nome,
                       j4.posicao,
                       j5.clube,
                       j5.nome,
                       j5.posicao,
                       j6.clube,
                       j6.nome,
                       j6.posicao,
                       j7.clube,
                       j7.nome,
                       j7.posicao,
                       j8.clube,
                       j8.nome,
                       j8.posicao,
                       j9.clube,
                       j9.nome,
                       j9.posicao,
                       j10.clube,
                       j10.nome,
                       j10.posicao,
                       j11.clube,
                       j11.nome,
                       j11.posicao,
                       j12.clube,
                       j12.nome,
                       j12.posicao
                  from (select * from jogadores where id = 324) j1,
                       (select * from jogadores where id = 325) j2,
                       (select * from jogadores where id = 381) j3,
                       (select * from jogadores where id = 384) j4,
                       (select * from jogadores where id = 385) j5,
                       (select * from jogadores where id = 388) j6,
                       (select * from jogadores where id = 378) j7,
                       (select * from jogadores where id = 379) j8,
                       (select * from jogadores where id = 421) j9,
                       (select * from jogadores where id = 425) j10,
                       (select * from jogadores where id = 345) j11,
                       (select * from jogadores where id = 401) j12;
                

                 

                Now I have my selection of the 12 most effective players for my team.

                FORMATION442
                GREATEST_RESULT134,71
                PRICE_TEAM175,7
                CLUBEAtlético-PR
                NOMEÉderson
                POSICAOATA
                CLUBEVitória
                NOMEMaxi Biancucchi
                POSICAOATA
                CLUBEInternacional
                NOMEFred
                POSICAOMEI
                CLUBEVasco
                NOMECarlos Alberto
                POSICAOMEI
                CLUBECruzeiro
                NOMENilton
                POSICAOMEI
                CLUBECorinthians
                NOMEGuilherme
                POSICAOMEI
                CLUBENáutico
                NOMEMaranhão
                POSICAOLAT
                CLUBEInternacional
                NOMEGabriel
                POSICAOLAT
                CLUBEFluminense
                NOMEDigão
                POSICAOZAG
                CLUBEGrêmio
                NOMEBressan
                POSICAOZAG
                CLUBEVitória
                NOMEWilson
                POSICAOGOL
                CLUBEFlamengo
                NOMEJaime De Almeida
                POSICAOTEC

                 

                Advance Grateful,

                Filippe

                • 5. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                  BrendanP

                  The plot thickens. With fixed positions, I would anchor on the combinations of those positions, like this, where I have added the two fixed positions 'CO' coach, 'GK' goalie:

                  WITH players_ranked AS (
                  SELECT id,
                        position,
                        price,
                        avg_points,
                        appearances,
                        Row_Number() OVER (ORDER BY price) rnk
                    FROM players
                  ), rsf (nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
                  SELECT 0, 0, p1.price + p2.price, p1.avg_points + p1.appearances + p2.avg_points + p2.appearances,
                        0, 0, 0, 0,
                        CAST (LPad (p1.id, 3, '0') || LPad (p2.id, 3, '0') AS VARCHAR2(4000)) path
                    FROM players p1
                    JOIN players p2
                      ON p1.position = 'CO'
                    AND p2.position = 'GK'
                  UNION ALL
                  SELECT p.rnk,
                        r.lev + 1,
                        r.tot_price + p.price,
                        r.tot_profit + p.avg_points + p.appearances,
                        r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
                        r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
                        r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
                        r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
                        r.path || LPad (p.id, 3, '0')
                    FROM rsf r
                    JOIN players_ranked p
                      ON p.rnk > r.nxt_id
                  WHERE EXISTS (SELECT 1 FROM formations WHERE
                                    centre_backs >= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
                                    wing_backs  >= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
                                    midfielders  >= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
                                    forwards    >= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
                                )
                    AND p.position NOT IN ('CO', 'GK')
                    AND r.tot_price + p.price <= 200
                  ) SEARCH DEPTH FIRST BY nxt_id SET line_no
                  , paths_ranked AS (
                  SELECT tot_price,
                        tot_profit,
                        Row_Number () OVER (ORDER BY tot_profit DESC) r_profit,
                        path
                    FROM rsf
                  WHERE lev = 10
                  ), top_ten_paths AS (
                  SELECT tot_price,
                        tot_profit,
                        r_profit,
                        path,
                        player_index
                    FROM paths_ranked
                    CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL < 13)
                  WHERE r_profit <= 10
                  ), top_ten_teams AS (
                  SELECT tot_price,
                        tot_profit,
                        r_profit,
                        path,
                        player_index,
                        Substr (path, (player_index - 1) * 3 + 1, 3) player_id
                    FROM top_ten_paths
                  )
                  SELECT t.tot_profit,
                        t.tot_price,
                        t.r_profit rnk,
                        p.position,
                        t.player_id p_id,
                        p.player_name,
                        p.club_name,
                        p.price,
                        p.avg_points,
                        p.appearances apps,
                        p.avg_points * p.appearances profit
                    FROM top_ten_teams t
                    JOIN players p
                      ON p.id = t.player_id
                  ORDER BY t.r_profit, t.path, p.position, t.player_index
                  
                  
                  

                   

                  Here is the first ranked team from your sample data. The top ten were returned in 0.6 seconds. Can you supply the complete data set, or a link to it, so I can test properly?

                  TOT_PROFIT  TOT_PRICE        RNK PO P_ID PLAYER_NAME          CLUB_NAME            PRICE AVG_POINTS       APPS     PROFIT
                  ---------- ---------- ---------- -- ---- -------------------- --------------- ---------- ---------- ---------- ----------
                      116.35     146.79          1 CB 001  Bressan              Grêmio               10.85        5.9          4       23.6
                                                      003  Cléber               Ponte Preta          14.61       5.78          5       28.9
                                                      002  Bruno Rodrigo        Cruzeiro             15.47       5.28          5       26.4
                                                   CO 015  Caio Júnior          Vitória               11.4       4.45          5      22.25
                                                   FW 011  Chiquinho            Ponte Preta           9.97       4.64          5       23.2
                                                   GK 018  Cássio               Corinthians          12.51       3.74          5       18.7
                                                   MF 010  Correa               Portuguesa            8.44        5.6          4       22.4
                                                      008  Cicinho              Ponte Preta          11.42       4.72          5       23.6
                                                      007  Carlos Alberto       Vasco                15.01       6.75          2       13.5
                                                      006  Alex                 Coritiba             16.98       5.08          5       25.4
                                                   WB 004  Auremir              Náutico               7.73       5.48          4      21.92
                                                      005  Carlinhos            Fluminense            12.4       6.93          3      20.79

                  I notice I have switched to profit as sum in the body of the query but forgotten to do the same in the main select.

                  • 6. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                    Filippe Soares Roza

                    Thanks, BredanP. I'm analyzing your query. Follows the data.

                     

                    esquema table

                    prompt PL/SQL Developer import file
                    prompt Created on quinta-feira, 20 de junho de 2013 by x100370
                    set feedback off
                    set define off
                    prompt Creating ESQUEMA...
                    create table ESQUEMA
                    (
                      ESQUEMA   INTEGER,
                      ZAGUEIROS INTEGER,
                      LATERAIS  INTEGER,
                      MEIAS     INTEGER,
                      ATACANTES INTEGER,
                      P1        CHAR(3),
                      P2        CHAR(3),
                      P3        CHAR(3),
                      P4        CHAR(3),
                      P5        CHAR(3),
                      P6        CHAR(3),
                      P7        CHAR(3),
                      P8        CHAR(3),
                      P9        CHAR(3),
                      P10       CHAR(3)
                    )
                    ;
                    create index P10_I on ESQUEMA (P10);
                    create index P1_I on ESQUEMA (P1);
                    create index P2_I on ESQUEMA (P2);
                    create index P3_I on ESQUEMA (P3);
                    create index P4_I on ESQUEMA (P4);
                    create index P5_I on ESQUEMA (P5);
                    create index P6_I on ESQUEMA (P6);
                    create index P7_I on ESQUEMA (P7);
                    create index P8_I on ESQUEMA (P8);
                    create index P9_I on ESQUEMA (P9);
                    
                    
                    prompt Truncating ESQUEMA...
                    truncate table ESQUEMA;
                    prompt Loading ESQUEMA...
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (442, 2, 2, 4, 2, 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA');
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (433, 2, 2, 3, 3, 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA', 'ATA');
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (532, 3, 2, 3, 2, 'ZAG', 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA');
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (352, 3, null, 5, 2, 'ZAG', 'ZAG', 'ZAG', 'MEI', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA');
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (343, 3, null, 4, 3, 'ZAG', 'ZAG', 'ZAG', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA', 'ATA', 'ATA');
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (451, 2, 2, 5, 1, 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA');
                    insert into ESQUEMA (ESQUEMA, ZAGUEIROS, LATERAIS, MEIAS, ATACANTES, P1, P2, P3, P4, P5, P6, P7, P8, P9, P10)
                    values (541, 3, 2, 4, 1, 'ZAG', 'ZAG', 'ZAG', 'LAT', 'LAT', 'MEI', 'MEI', 'MEI', 'MEI', 'ATA');
                    commit;
                    prompt 7 records loaded
                    set feedback on
                    set define on
                    prompt Done.
                    

                     

                    jogadores table

                    prompt PL/SQL Developer import file
                    prompt Created on quinta-feira, 20 de junho de 2013 by x100370
                    set feedback off
                    set define off
                    prompt Creating JOGADORES...
                    create table JOGADORES
                    (
                      ID      INTEGER not null,
                      CLUBE   VARCHAR2(100),
                      NOME    VARCHAR2(100),
                      POSICAO CHAR(3),
                      PRECO   NUMBER(4,2),
                      MEDIA   NUMBER(4,2),
                      JOGOS   INTEGER
                    )
                    ;
                    alter table JOGADORES
                      add constraint HR_JOGADORES_PK primary key (ID);
                    alter table JOGADORES
                      add constraint HR_JOGADORES_UK unique (CLUBE, NOME, POSICAO);
                    create index HR_JOGADORES_POS_I on JOGADORES (POSICAO);
                    
                    
                    prompt Truncating JOGADORES...
                    truncate table JOGADORES;
                    prompt Loading JOGADORES...
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (324, 'Atlético-PR', 'Éderson', 'ATA', 17.12, 10.12, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (325, 'Vitória', 'Maxi Biancucchi', 'ATA', 19.62, 10.05, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (326, 'Fluminense', 'Rafael Sobis', 'ATA', 23.03, 9.55, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (327, 'Bahia', 'Fernandão', 'ATA', 13.28, 8.22, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (328, 'São Paulo', 'Luis Fabiano', 'ATA', 21.54, 7.58, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (329, 'Botafogo', 'Rafael Marques', 'ATA', 19.74, 6.68, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (330, 'Cruzeiro', 'Dagoberto', 'ATA', 22.11, 5.94, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (331, 'Náutico', 'Rogério', 'ATA', 10.62, 5.7, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (332, 'Flamengo', 'Hernane', 'ATA', 13.87, 4.98, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (333, 'Crisciúma', 'Lins', 'ATA', 18.4, 4.9, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (334, 'Santos', 'Neilton', 'ATA', 6.38, 4.88, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (335, 'Fluminense', 'Samuel', 'ATA', 10.01, 4.87, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (336, 'Ponte Preta', 'Chiquinho', 'ATA', 9.97, 4.64, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (337, 'Atlético-MG', 'Luan', 'ATA', 13.18, 4.55, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (338, 'Ponte Preta', 'William', 'ATA', 13.93, 4.44, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (339, 'Botafogo', 'Vitinho', 'ATA', 10.2, 4.04, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (340, 'Coritiba', 'Deivid', 'ATA', 15.9, 3.76, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (341, 'Grêmio', 'Barcos', 'ATA', 18.96, 3.67, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (342, 'Atlético-MG', 'Jô', 'ATA', 13.93, 3.4, 2);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (343, 'São Paulo', 'Osvaldo', 'ATA', 13.64, 3.12, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (344, 'Cruzeiro', 'Fábio', 'GOL', 20.9, 7.94, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (345, 'Vitória', 'Wilson', 'GOL', 12.39, 7.94, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (346, 'Coritiba', 'Vanderlei', 'GOL', 18.58, 7.76, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (347, 'Atlético-MG', 'Victor', 'GOL', 11.63, 4.67, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (348, 'Bahia', 'Marcelo Lomba', 'GOL', 13.64, 4.5, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (349, 'Botafogo', 'Renan', 'GOL', 6.77, 4.37, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (350, 'Flamengo', 'Felipe', 'GOL', 15.26, 4.14, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (351, 'Grêmio', 'Dida', 'GOL', 11.32, 3.75, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (352, 'Corinthians', 'Cássio', 'GOL', 12.51, 3.74, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (353, 'Vasco', 'Michel Alves', 'GOL', 8.99, 3.48, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (354, 'Crisciúma', 'Bruno', 'GOL', 10.66, 3.2, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (355, 'Internacional', 'Muriel', 'GOL', 9.81, 3.1, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (356, 'Santos', 'Rafael', 'GOL', 17.82, 3, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (357, 'Atlético-PR', 'Weverton', 'GOL', 6.16, 2.48, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (358, 'Fluminense', 'Ricardo Berna', 'GOL', 4.6, 2.42, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (359, 'Portuguesa', 'Gledson', 'GOL', 4.52, 2.1, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (360, 'São Paulo', 'Rogério Ceni', 'GOL', 14.2, 1.17, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (361, 'Portuguesa', 'Ivan', 'LAT', 7.55, 13.2, 1);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (362, 'Vasco', 'Elsinho', 'LAT', 14.68, 8.5, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (363, 'Cruzeiro', 'Egídio', 'LAT', 14.82, 7.52, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (364, 'Fluminense', 'Carlinhos', 'LAT', 12.4, 6.93, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (365, 'Náutico', 'Auremir', 'LAT', 7.73, 5.48, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (366, 'Cruzeiro', 'Mayke', 'LAT', 3.74, 5.25, 2);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (367, 'Portuguesa', 'Luis Ricardo', 'LAT', 8.58, 4.67, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (368, 'Atlético-MG', 'Richarlyson', 'LAT', 10.2, 4.67, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (369, 'Internacional', 'Fabrício', 'LAT', 8.76, 4.57, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (370, 'São Paulo', 'Juan', 'LAT', 7.89, 4.57, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (371, 'São Paulo', 'Paulo Miranda', 'LAT', 10.53, 4.54, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (372, 'Flamengo', 'João Paulo', 'LAT', 7.15, 4.53, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (373, 'São Paulo', 'Rodrigo Caio', 'LAT', 11.92, 4.52, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (374, 'Coritiba', 'Victor Ferraz', 'LAT', 13.04, 4.2, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (375, 'Bahia', 'Jussandro', 'LAT', 6.94, 4.1, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (376, 'Santos', 'Rafael Galhardo', 'LAT', 12.88, 4.04, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (377, 'Goiás', 'William Matheus', 'LAT', 5.87, 4.02, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (378, 'Náutico', 'Maranhão', 'LAT', 6.53, 4.02, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (379, 'Internacional', 'Gabriel', 'LAT', 11.81, 3.38, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (380, 'Goiás', 'Vítor', 'LAT', 8.77, 3.36, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (381, 'Internacional', 'Fred', 'MEI', 30.28, 8.92, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (382, 'Grêmio', 'Zé Roberto', 'MEI', 25.93, 8.78, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (383, 'Internacional', 'Otavinho', 'MEI', 7.62, 8.07, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (384, 'Vasco', 'Carlos Alberto', 'MEI', 15.01, 6.75, 2);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (385, 'Cruzeiro', 'Nilton', 'MEI', 22.39, 6.46, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (386, 'Coritiba', 'Júnior Urso', 'MEI', 14.38, 6.22, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (387, 'Crisciúma', 'João Vitor', 'MEI', 13.27, 6.04, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (388, 'Corinthians', 'Guilherme', 'MEI', 8.83, 5.87, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (389, 'Corinthians', 'Ralf', 'MEI', 19.65, 5.7, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (390, 'Vitória', 'Escudero', 'MEI', 16.38, 5.68, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (391, 'Portuguesa', 'Correa', 'MEI', 8.44, 5.6, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (392, 'Portuguesa', 'Souza', 'MEI', 12.62, 5.17, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (393, 'Coritiba', 'Alex', 'MEI', 16.98, 5.08, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (394, 'Grêmio', 'Souza', 'MEI', 13.8, 4.98, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (395, 'Ponte Preta', 'Cicinho', 'MEI', 11.42, 4.72, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (396, 'Botafogo', 'Fellype Gabriel', 'MEI', 8.6, 4.47, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (397, 'Atlético-PR', 'João Paulo', 'MEI', 10.56, 4.38, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (398, 'Vasco', 'Sandro Silva', 'MEI', 10.76, 4.28, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (399, 'Santos', 'Cícero', 'MEI', 14.15, 4.18, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (400, 'Fluminense', 'Wagner', 'MEI', 8.55, 4.13, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (401, 'Flamengo', 'Jaime De Almeida', 'TEC', 11.56, 8.03, 1);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (402, 'Cruzeiro', 'Marcelo Oliveira', 'TEC', 16.11, 5.43, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (403, 'Fluminense', 'Abel Braga', 'TEC', 17.51, 5.36, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (404, 'Internacional', 'Dunga', 'TEC', 14.22, 4.63, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (405, 'Vitória', 'Caio Júnior', 'TEC', 11.4, 4.45, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (406, 'Grêmio', 'Vanderlei Luxemburgo', 'TEC', 15.77, 4.42, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (407, 'São Paulo', 'Ney Franco', 'TEC', 15.15, 4.39, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (408, 'Náutico', 'Levi Gomes', 'TEC', 7.08, 4.2, 2);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (409, 'Atlético-PR', 'Ricardo Drubscky', 'TEC', 7.96, 3.92, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (410, 'Coritiba', 'Marquinhos Santos', 'TEC', 10.59, 3.89, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (411, 'Vasco', 'Paulo Autuori', 'TEC', 13.13, 3.61, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (412, 'Portuguesa', 'Edson Pimenta', 'TEC', 3.67, 3.26, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (413, 'Botafogo', 'Oswaldo De Oliveira', 'TEC', 10.77, 3.23, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (414, 'Corinthians', 'Tite', 'TEC', 13.68, 3.17, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (415, 'Santos', 'Claudinei Oliveira', 'TEC', 11.92, 3.17, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (416, 'Bahia', 'Cristóvão Borges', 'TEC', 8.27, 2.92, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (417, 'Crisciúma', 'Vadão', 'TEC', 7.04, 2.86, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (418, 'Goiás', 'Enderson Moreira', 'TEC', 6.8, 2.53, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (419, 'Atlético-MG', 'Cuca', 'TEC', 12.62, 2.32, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (420, 'Ponte Preta', 'Zé Sérgio', 'TEC', 6.85, .75, 1);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (421, 'Fluminense', 'Digão', 'ZAG', 9.31, 9.27, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (422, 'Flamengo', 'Samir', 'ZAG', 2.67, 6.8, 1);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (423, 'Cruzeiro', 'Dedé', 'ZAG', 22.54, 6.4, 5);
                    commit;
                    prompt 100 records committed...
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (424, 'São Paulo', 'Lúcio', 'ZAG', 21.71, 6.02, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (425, 'Grêmio', 'Bressan', 'ZAG', 10.85, 5.9, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (426, 'Atlético-PR', 'Manoel', 'ZAG', 16.99, 5.88, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (427, 'Ponte Preta', 'Cléber', 'ZAG', 14.61, 5.78, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (428, 'Cruzeiro', 'Bruno Rodrigo', 'ZAG', 15.47, 5.28, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (429, 'Santos', 'Edu Dracena', 'ZAG', 16.82, 4.97, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (430, 'Náutico', 'William Alves', 'ZAG', 5.56, 4.43, 3);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (431, 'Fluminense', 'Gum', 'ZAG', 12.18, 4.22, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (432, 'Flamengo', 'Wallace', 'ZAG', 4.29, 4.2, 2);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (433, 'Náutico', 'João Filipe', 'ZAG', 5.47, 4.1, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (434, 'Grêmio', 'Werley', 'ZAG', 15.9, 4.03, 4);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (435, 'Corinthians', 'Gil', 'ZAG', 13.23, 3.98, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (436, 'Vitória', 'Gabriel Paulista', 'ZAG', 11.77, 3.94, 5);
                    insert into JOGADORES (ID, CLUBE, NOME, POSICAO, PRECO, MEDIA, JOGOS)
                    values (437, 'Goiás', 'Ernando', 'ZAG', 10.24, 3.74, 5);
                    commit;
                    prompt 114 records loaded
                    set feedback on
                    set define on
                    prompt Done.
                    

                     

                    Grateful,

                    Filippe

                    • 7. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                      Filippe Soares Roza

                      BrendanP,

                       

                      Your query is executing for 101 minutes and has not returned.

                      Will my laptop?

                      You could run with the data sent?

                      • 8. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                        BrendanP

                        I tried it last night but got a 'could not extend temp space' error. I've also realised for this type of problem, where the number of items is fixed, you could  join 12 table instances instead of using recursion - but it gets pretty messy. Am also wondering whether a non-SQL solution might be necessary for performance, although likely more complicated. I'll look further as I get time. You might want to try gradually scaling up the problem size - would be interested to hear how you get on.

                        • 9. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                          Filippe Soares Roza

                          I'm curious to find a solution. Analyzing ...

                           

                          Thanks

                          Filippe

                          • 10. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                            BrendanP

                            The solution I posted earlier using recursive SQL gives the guaranteed best solution but, like any guaranteed method, will take a long time for these computationally hard problems as the problem size goes up. It can be modified to explore promising paths early while saving time by ignoring poor-seeming paths. This results in much improved performance but with the solutions no longer guaranteed to be the best. I rank the partial solutions in the recursive branch projection and limit the paths progressed to the best :KEEP_NUM. On OP's full data set (I changed a few details), I get the same best 10 using :KEEP_NUM of 100 and 1000, in 3s and 30s respectively.

                             

                            This approach can also be used for other combinatorial problems.

                             

                            Top ten solutions

                             

                             

                            
                            WITH players_ranked AS (
                            SELECT id,
                                   position,
                                   price,
                                   avg_points,
                                   appearances,
                                   Row_Number() OVER (ORDER BY avg_points DESC) rnk
                              FROM players
                            ), rsf (dt, nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
                            SELECT Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC),
                                   0.0001 * Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC),
                                   0, p1.price + p2.price, p1.avg_points + p2.avg_points,
                                   0, 0, 0, 0,
                                   CAST (LPad (p1.id, 3, '0') || LPad (p2.id, 3, '0') AS VARCHAR2(4000)) path
                              FROM players p1
                              JOIN players p2
                                ON p1.position = 'CO'
                               AND p2.position = 'GK'
                            UNION ALL
                            SELECT Row_Number() OVER (ORDER BY r.tot_profit + p.avg_points DESC),-- * r.dt,
                                   p.rnk,
                                   r.lev + 1,
                                   r.tot_price + p.price,
                                   r.tot_profit + p.avg_points,
                                   r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
                                   r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
                                   r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
                                   r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
                                   r.path || LPad (p.id, 3, '0')
                              FROM rsf r
                              JOIN players_ranked p
                                ON p.rnk > r.nxt_id
                            WHERE EXISTS (SELECT 1 FROM formations WHERE
                                               centre_backs >= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
                                               wing_backs   >= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
                                               midfielders  >= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
                                               forwards     >= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
                                          )
                              AND p.position NOT IN ('CO', 'GK')
                              AND r.tot_price + p.price <= 20000
                              AND r.dt < :KEEP_NUM
                            ) SEARCH DEPTH FIRST BY nxt_id SET line_no
                            , paths_ranked AS (
                            SELECT tot_price,
                                   tot_profit,
                                   Row_Number () OVER (ORDER BY tot_profit DESC) r_profit,
                                   path
                              FROM rsf
                            WHERE lev = 10
                            ), top_ten_paths AS (
                            SELECT tot_price,
                                   tot_profit,
                                   r_profit,
                                   path,
                                   player_index
                              FROM paths_ranked
                              CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL < 13)
                            WHERE r_profit <= 10
                            ), top_ten_teams AS (
                            SELECT tot_price,
                                   tot_profit,
                                   r_profit,
                                   path,
                                   player_index,
                                   Substr (path, (player_index - 1) * 3 + 1, 3) player_id
                              FROM top_ten_paths
                            )
                            SELECT t.tot_profit,
                                   t.tot_price,
                                   t.r_profit rnk,
                                   p.position,
                                   t.player_id p_id,
                                   p.player_name,
                                   p.club_name,
                                   p.price,
                                   p.avg_points
                              FROM top_ten_teams t
                              JOIN players p
                                ON p.id = t.player_id
                            ORDER BY t.r_profit, t.path, p.position, t.player_index
                            

                             

                            Here are the test data and the first of the top ten solutions found:

                            Input Formations
                            
                            ID  CENTRE_BACKS WING_BACKS MIDFIELDERS   FORWARDS
                            --- ------------ ---------- ----------- ----------
                            343            3          0           4          3
                            352            3          0           5          2
                            433            2          2           3          3
                            442            2          2           4          2
                            451            2          2           5          1
                            532            3          2           3          2
                            541            3          2           4          1
                            
                            7 rows selected.
                            
                            Input Players
                            
                            ID  CLUB_NAME       PLAYER_NAME          PO      PRICE AVG_POINTS
                            --- --------------- -------------------- -- ---------- ----------
                            089 Portuguesa      Edson Pimenta        CO        367        326
                            054 Goiás           William Matheus      WB        587        402
                            022 Vitória         Wilson               GK       1239        794
                            060 Internacional   Otavinho             MF        762        807
                            004 Bahia           Fernandão            FW       1328        822
                            055 Náutico         Maranhão             WB        653        402
                            011 Santos          Neilton              FW        638        488
                            110 Náutico         João Filipe          CB        547        410
                            098 Fluminense      Digão                CB        931        927
                            001 Atlético-PR     Éderson              FW       1712       1012
                            052 Bahia           Jussandro            WB        694        410
                            042 Náutico         Auremir              WB        773        548
                            043 Cruzeiro        Mayke                WB        374        525
                            008 Náutico         Rogério              FW       1062        570
                            065 Corinthians     Guilherme            MF        883        587
                            068 Portuguesa      Correa               MF        844        560
                            026 Botafogo        Renan                GK        677        437
                            099 Flamengo        Samir                CB        267        680
                            040 Cruzeiro        Egídio               WB       1482        752
                            086 Atlético-PR     Ricardo Drubscky     CO        796        392
                            107 Náutico         William Alves        CB        556        443
                            013 Ponte Preta     Chiquinho            FW        997        464
                            039 Vasco           Elsinho              WB       1468        850
                            064 Crisciúma       João Vitor           MF       1327        604
                            102 Grêmio          Bressan              CB       1085        590
                            063 Coritiba        Júnior Urso          MF       1438        622
                            048 São Paulo       Paulo Miranda        WB       1053        454
                            035 Fluminense      Ricardo Berna        GK        460        242
                            023 Coritiba        Vanderlei            GK       1858        776
                            046 Internacional   Fabrício             WB        876        457
                            073 Botafogo        Fellype Gabriel      MF        860        447
                            074 Atlético-PR     João Paulo           MF       1056        438
                            072 Ponte Preta     Cicinho              MF       1142        472
                            002 Vitória         Maxi Biancucchi      FW       1962       1005
                            094 Crisciúma       Vadão                CO        704        286
                            034 Atlético-PR     Weverton             GK        616        248
                            075 Vasco           Sandro Silva         MF       1076        428
                            016 Botafogo        Vitinho              FW       1020        404
                            104 Ponte Preta     Cléber               CB       1461        578
                            109 Flamengo        Wallace              CB        429        420
                            082 Vitória         Caio Júnior          CO       1140        445
                            030 Vasco           Michel Alves         GK        899        348
                            057 Goiás           Vítor                WB        877        336
                            049 Flamengo        João Paulo           WB        715        453
                            021 Cruzeiro        Fábio                GK       2090        794
                            050 São Paulo       Rodrigo Caio         WB       1192        452
                            095 Goiás           Enderson Moreira     CO        680        253
                            036 Portuguesa      Gledson              GK        452        210
                            087 Coritiba        Marquinhos Santos    CO       1059        389
                            114 Goiás           Ernando              CB       1024        374
                            009 Flamengo        Hernane              FW       1387        498
                            093 Bahia           Cristóvão Borges     CO        827        292
                            038 Portuguesa      Ivan                 WB        755       1320
                            047 São Paulo       Juan                 WB        789        457
                            067 Vitória         Escudero             MF       1638        568
                            103 Atlético-PR     Manoel               CB       1699        588
                            105 Cruzeiro        Bruno Rodrigo        CB       1547        528
                            006 Botafogo        Rafael Marques       FW       1974        668
                            079 Cruzeiro        Marcelo Oliveira     CO       1611        543
                            041 Fluminense      Carlinhos            WB       1240        693
                            113 Vitória         Gabriel Paulista     CB       1177        394
                            003 Fluminense      Rafael Sobis         FW       2303        955
                            025 Bahia           Marcelo Lomba        GK       1364        450
                            069 Portuguesa      Souza                MF       1262        517
                            044 Portuguesa      Luis Ricardo         WB        858        467
                            081 Internacional   Dunga                CO       1422        463
                            051 Coritiba        Victor Ferraz        WB       1304        420
                            024 Atlético-MG     Victor               GK       1163        467
                            015 Ponte Preta     William              FW       1393        444
                            053 Santos          Rafael Galhardo      WB       1288        404
                            112 Corinthians     Gil                  CB       1323        398
                            031 Crisciúma       Bruno                GK       1066        320
                            090 Botafogo        Oswaldo De Oliveira  CO       1077        323
                            070 Coritiba        Alex                 MF       1698        508
                            029 Corinthians     Cássio               GK       1251        374
                            076 Santos          Cícero               MF       1415        418
                            058 Internacional   Fred                 MF       3028        892
                            012 Fluminense      Samuel               FW       1001        487
                            066 Corinthians     Ralf                 MF       1965        570
                            077 Fluminense      Wagner               MF        855        413
                            084 São Paulo       Ney Franco           CO       1515        439
                            071 Grêmio          Souza                MF       1380        498
                            062 Cruzeiro        Nilton               MF       2239        646
                            056 Internacional   Gabriel              WB       1181        338
                            100 Cruzeiro        Dedé                 CB       2254        640
                            005 São Paulo       Luis Fabiano         FW       2154        758
                            101 São Paulo       Lúcio                CB       2171        602
                            108 Fluminense      Gum                  CB       1218        422
                            014 Atlético-MG     Luan                 FW       1318        455
                            088 Vasco           Paulo Autuori        CO       1313        361
                            045 Atlético-MG     Richarlyson          WB       1020        467
                            027 Flamengo        Felipe               GK       1526        414
                            059 Grêmio          Zé Roberto           MF       2593        878
                            007 Cruzeiro        Dagoberto            FW       2211        594
                            010 Crisciúma       Lins                 FW       1840        490
                            028 Grêmio          Dida                 GK       1132        375
                            032 Internacional   Muriel               GK        981        310
                            080 Fluminense      Abel Braga           CO       1751        536
                            085 Náutico         Levi Gomes           CO        708        420
                            017 Coritiba        Deivid               FW       1590        376
                            091 Corinthians     Tite                 CO       1368        317
                            020 São Paulo       Osvaldo              FW       1364        312
                            083 Grêmio          Vanderlei Luxemburgo CO       1577        442
                            111 Grêmio          Werley               CB       1590        403
                            061 Vasco           Carlos Alberto       MF       1501        675
                            106 Santos          Edu Dracena          CB       1682        497
                            033 Santos          Rafael               GK       1782        300
                            092 Santos          Claudinei Oliveira   CO       1192        317
                            018 Grêmio          Barcos               FW       1896        367
                            096 Atlético-MG     Cuca                 CO       1262        232
                            078 Flamengo        Jaime De AlMFda      CO       1156        803
                            019 Atlético-MG     Jô                   FW       1393        340
                            037 São Paulo       Rogério Ceni         GK       1420        117
                            097 Ponte Preta     Zé Sérgio            CO        685         75
                            
                            114 rows selected.
                            
                            
                            PL/SQL procedure successfully completed.
                            
                            Elapsed: 00:00:00.01
                            Top ten solutions
                            
                            TOT_PROFIT  TOT_PRICE        RNK PO P_ID PLAYER_NAME          CLUB_NAME            PRICE AVG_POINTS
                            ---------- ---------- ---------- -- ---- -------------------- --------------- ---------- ----------
                                 10923      19027          1 CB 098  Digão                Fluminense             931        927
                                                                099  Samir                Flamengo               267        680
                                                             CO 078  Jaime De AlMFda      Flamengo              1156        803
                                                             FW 001  Éderson              Atlético-PR           1712       1012
                                                                002  Maxi Biancucchi      Vitória               1962       1005
                                                                003  Rafael Sobis         Fluminense            2303        955
                                                             GK 021  Fábio                Cruzeiro              2090        794
                                                             MF 058  Fred                 Internacional         3028        892
                                                                059  Zé Roberto           Grêmio                2593        878
                                                                060  Otavinho             Internacional          762        807
                                                             WB 038  Ivan                 Portuguesa             755       1320
                                                                039  Elsinho              Vasco                 1468        850
                            ...
                            
                            • 11. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                              Filippe Soares Roza

                              Returned ORA-01008: not all variables bound ...

                              player_index?

                              • 12. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                                BrendanP

                                Probably the bind variable I added. Replace it with a number like 100, or do this in sqlplus:

                                 

                                VAR KEEP_NUM NUMBER

                                BEGIN

                                  :KEEP_NUM := 100;

                                END;

                                /

                                • 13. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                                  Filippe Soares Roza

                                  I'm doing something wrong...

                                  Not return anything:

                                   

                                  WITH players_ranked AS
                                  (SELECT id,
                                           posicao,
                                           preco,
                                           media,
                                           jogos,
                                           Row_Number() OVER(ORDER BY media DESC) rnk
                                      FROM jogadores),
                                  rsf(dt,
                                  nxt_id,
                                  lev,
                                  tot_price,
                                  tot_profit,
                                  n_cb,
                                  n_wb,
                                  n_mf,
                                  n_fw,
                                  path) AS
                                  (SELECT Row_Number() OVER(ORDER BY(p1.media + p2.media) DESC),
                                           0.0001 * Row_Number() OVER(ORDER BY(p1.media + p2.media) DESC),
                                           0,
                                           p1.preco + p2.preco,
                                           p1.media + p2.media,
                                           0,
                                           0,
                                           0,
                                           0,
                                           CAST(LPad(p1.id, 3, '0') || LPad(p2.id, 3, '0') AS VARCHAR2(4000)) path
                                      FROM jogadores p1
                                      JOIN jogadores p2
                                        ON p1.posicao = 'CO'
                                       AND p2.posicao = 'GK'
                                    UNION ALL
                                    SELECT Row_Number() OVER(ORDER BY r.tot_profit + p.media DESC), -- * r.dt,
                                           p.rnk,
                                           r.lev + 1,
                                           r.tot_price + p.preco,
                                           r.tot_profit + p.media,
                                           r.n_cb + CASE p.posicao
                                             WHEN 'CB' THEN
                                              1
                                             ELSE
                                              0
                                           END,
                                           r.n_wb + CASE p.posicao
                                             WHEN 'WB' THEN
                                              1
                                             ELSE
                                              0
                                           END,
                                           r.n_mf + CASE p.posicao
                                             WHEN 'MF' THEN
                                              1
                                             ELSE
                                              0
                                           END,
                                           r.n_fw + CASE p.posicao
                                             WHEN 'FW' THEN
                                              1
                                             ELSE
                                              0
                                           END,
                                           r.path || LPad(p.id, 3, '0')
                                      FROM rsf r
                                      JOIN players_ranked p
                                        ON p.rnk > r.nxt_id
                                     WHERE EXISTS (SELECT 1
                                              FROM esquema
                                             WHERE laterais >= r.n_cb + CASE p.posicao
                                                     WHEN 'CB' THEN
                                                      1
                                                     ELSE
                                                      0
                                                   END
                                               AND zagueiros >= r.n_wb + CASE p.posicao
                                                     WHEN 'WB' THEN
                                                      1
                                                     ELSE
                                                      0
                                                   END
                                               AND meias >= r.n_mf + CASE p.posicao
                                                     WHEN 'MF' THEN
                                                      1
                                                     ELSE
                                                      0
                                                   END
                                               AND atacantes >= r.n_fw + CASE p.posicao
                                                     WHEN 'FW' THEN
                                                      1
                                                     ELSE
                                                      0
                                                   END)
                                       AND p.posicao NOT IN ('CO', 'GK')
                                       AND r.tot_price + p.preco <= 20000
                                       AND r.dt < 100 --:KEEP_NUM SQLPLUS VAR KEEP_NUM NUMBER BEGIN :KEEP_NUM := 100; END; /
                                    ) SEARCH DEPTH FIRST BY nxt_id SET line_no,
                                  paths_ranked AS
                                  (SELECT tot_price,
                                           tot_profit,
                                           Row_Number() OVER(ORDER BY tot_profit DESC) r_profit,
                                           path
                                      FROM rsf
                                     WHERE lev = 10),
                                  top_ten_paths AS
                                  (SELECT tot_price, tot_profit, r_profit, path, player_index -- ***
                                      FROM paths_ranked
                                     CROSS JOIN (SELECT LEVEL player_index FROM DUAL CONNECT BY LEVEL < 13)
                                     WHERE r_profit <= 10),
                                  top_ten_teams AS
                                  (SELECT tot_price,
                                           tot_profit,
                                           r_profit,
                                           path,
                                           player_index,
                                           Substr(path, (player_index - 1) * 3 + 1, 3) player_id
                                      FROM top_ten_paths)
                                  SELECT t.tot_profit,
                                         t.tot_price,
                                         t.r_profit   rnk,
                                         p.posicao,
                                         t.player_id  p_id,
                                         p.nome,
                                         p.clube,
                                         p.preco,
                                         p.media
                                    FROM top_ten_teams t
                                    JOIN jogadores p
                                      ON p.id = t.player_id
                                  ORDER BY t.r_profit, t.path, p.posicao, t.player_index
                                  

                                   

                                  Filippe

                                  • 14. Re: Processing Cost - How to catch a soccer team with the highest combined score?
                                    BrendanP

                                    Not sure, have you swapped your codes for mine? Anyway, here is the complete code that I have just run on a different computer, and it worked, so you could start there...

                                     

                                    SET TRIMSPOOL ON
                                    SET PAGES 10000
                                    SET lines 1000
                                    SET SERVEROUTPUT ON
                                    SPOOL ..\lst\FF_DDL
                                    SELECT 'Start: ' || To_Char(SYSDATE,'DD-MON-YYYY HH24:MI:SS') FROM DUAL;
                                    
                                    DROP TABLE formations
                                    /
                                    CREATE TABLE formations (
                                            id              VARCHAR2(3) PRIMARY KEY,  
                                            centre_backs    INTEGER,  
                                            wing_backs      INTEGER,  
                                            midfielders     INTEGER,  
                                            forwards        INTEGER  
                                    )  
                                    /
                                    DROP TABLE players
                                    /
                                    CREATE TABLE players (
                                            id              VARCHAR2(3) PRIMARY KEY,
                                            club_name       VARCHAR2(30),
                                            player_name     VARCHAR2(30),
                                            position        VARCHAR2(2),
                                            price           NUMBER,
                                            avg_points      NUMBER,
                                            appearances     NUMBER
                                    )  
                                    /
                                    EXECUTE Utils.Clear_Log;
                                    DECLARE
                                    
                                      i     PLS_INTEGER := 0;
                                      PROCEDURE Ins_Formation (
                                                            p_id             VARCHAR2,
                                                            p_centre_backs     PLS_INTEGER,
                                                            p_wing_backs     PLS_INTEGER,
                                                            p_midfielders     PLS_INTEGER,
                                                            p_forwards     PLS_INTEGER) IS
                                      BEGIN
                                    
                                        INSERT INTO formations VALUES (p_id, p_centre_backs, p_wing_backs, p_midfielders, p_forwards);
                                    
                                      END Ins_Formation;
                                    
                                      PROCEDURE Ins_Player (p_club_name    VARCHAR2,
                                                            p_player_name  VARCHAR2, 
                                                            p_position     VARCHAR2,
                                                            p_price        NUMBER,
                                                            p_avg_points   NUMBER,
                                                            p_appearances  PLS_INTEGER) IS
                                      BEGIN
                                    
                                        i := i + 1;
                                        INSERT INTO players VALUES (LPad (i, 3, '0'), p_club_name, p_player_name, p_position, 100*p_price, 100*p_avg_points, 100*p_appearances);
                                    
                                      END Ins_Player;
                                    
                                    BEGIN
                                    
                                      DELETE formations;
                                      Ins_Formation ('442', 2, 2, 4, 2);
                                      Ins_Formation ('433', 2, 2, 3, 3);
                                      Ins_Formation ('532', 3, 2, 3, 2);
                                      Ins_Formation ('352', 3, 0, 5, 2);
                                      Ins_Formation ('343', 3, 0, 4, 3);
                                      Ins_Formation ('451', 2, 2, 5, 1);
                                      Ins_Formation ('541', 3, 2, 4, 1);
                                    
                                      DELETE players;
                                      Ins_Player ('Atlético-PR', 'Éderson', 'FW', 17.12, 10.12, 5);  
                                      Ins_Player ('Vitória', 'Maxi Biancucchi', 'FW', 19.62, 10.05, 4);  
                                      Ins_Player ('Fluminense', 'Rafael Sobis', 'FW', 23.03, 9.55, 4);  
                                      Ins_Player ('Bahia', 'Fernandão', 'FW', 13.28, 8.22, 5);  
                                      Ins_Player ('São Paulo', 'Luis Fabiano', 'FW', 21.54, 7.58, 4);  
                                      Ins_Player ('Botafogo', 'Rafael Marques', 'FW', 19.74, 6.68, 5);  
                                      Ins_Player ('Cruzeiro', 'Dagoberto', 'FW', 22.11, 5.94, 5);  
                                      Ins_Player ('Náutico', 'Rogério', 'FW', 10.62, 5.7, 5);  
                                      Ins_Player ('Flamengo', 'Hernane', 'FW', 13.87, 4.98, 5);  
                                      Ins_Player ('Crisciúma', 'Lins', 'FW', 18.4, 4.9, 5);  
                                      Ins_Player ('Santos', 'Neilton', 'FW', 6.38, 4.88, 4);  
                                      Ins_Player ('Fluminense', 'Samuel', 'FW', 10.01, 4.87, 3);  
                                      Ins_Player ('Ponte Preta', 'Chiquinho', 'FW', 9.97, 4.64, 5);  
                                      Ins_Player ('Atlético-MG', 'Luan', 'FW', 13.18, 4.55, 4);  
                                      Ins_Player ('Ponte Preta', 'William', 'FW', 13.93, 4.44, 5);  
                                      Ins_Player ('Botafogo', 'Vitinho', 'FW', 10.2, 4.04, 5);  
                                      Ins_Player ('Coritiba', 'Deivid', 'FW', 15.9, 3.76, 5);  
                                      Ins_Player ('Grêmio', 'Barcos', 'FW', 18.96, 3.67, 4);  
                                      Ins_Player ('Atlético-MG', 'Jô', 'FW', 13.93, 3.4, 2);  
                                      Ins_Player ('São Paulo', 'Osvaldo', 'FW', 13.64, 3.12, 5);  
                                      Ins_Player ('Cruzeiro', 'Fábio', 'GK', 20.9, 7.94, 5);  
                                      Ins_Player ('Vitória', 'Wilson', 'GK', 12.39, 7.94, 5);  
                                      Ins_Player ('Coritiba', 'Vanderlei', 'GK', 18.58, 7.76, 5);  
                                      Ins_Player ('Atlético-MG', 'Victor', 'GK', 11.63, 4.67, 4);  
                                      Ins_Player ('Bahia', 'Marcelo Lomba', 'GK', 13.64, 4.5, 5);  
                                      Ins_Player ('Botafogo', 'Renan', 'GK', 6.77, 4.37, 4);  
                                      Ins_Player ('Flamengo', 'Felipe', 'GK', 15.26, 4.14, 5);  
                                      Ins_Player ('Grêmio', 'Dida', 'GK', 11.32, 3.75, 4);  
                                      Ins_Player ('Corinthians', 'Cássio', 'GK', 12.51, 3.74, 5);  
                                      Ins_Player ('Vasco', 'Michel Alves', 'GK', 8.99, 3.48, 5);  
                                      Ins_Player ('Crisciúma', 'Bruno', 'GK', 10.66, 3.2, 5);  
                                      Ins_Player ('Internacional', 'Muriel', 'GK', 9.81, 3.1, 4);  
                                      Ins_Player ('Santos', 'Rafael', 'GK', 17.82, 3, 5);  
                                      Ins_Player ('Atlético-PR', 'Weverton', 'GK', 6.16, 2.48, 5);  
                                      Ins_Player ('Fluminense', 'Ricardo Berna', 'GK', 4.6, 2.42, 4);  
                                      Ins_Player ('Portuguesa', 'Gledson', 'GK', 4.52, 2.1, 4);  
                                      Ins_Player ('São Paulo', 'Rogério Ceni', 'GK', 14.2, 1.17, 4);  
                                      Ins_Player ('Portuguesa', 'Ivan', 'WB', 7.55, 13.2, 1);  
                                      Ins_Player ('Vasco', 'Elsinho', 'WB', 14.68, 8.5, 4);  
                                      Ins_Player ('Cruzeiro', 'Egídio', 'WB', 14.82, 7.52, 5);  
                                      Ins_Player ('Fluminense', 'Carlinhos', 'WB', 12.4, 6.93, 3);  
                                      Ins_Player ('Náutico', 'Auremir', 'WB', 7.73, 5.48, 4);  
                                      Ins_Player ('Cruzeiro', 'Mayke', 'WB', 3.74, 5.25, 2);  
                                      Ins_Player ('Portuguesa', 'Luis Ricardo', 'WB', 8.58, 4.67, 3);  
                                      Ins_Player ('Atlético-MG', 'Richarlyson', 'WB', 10.2, 4.67, 3);  
                                      Ins_Player ('Internacional', 'Fabrício', 'WB', 8.76, 4.57, 4);  
                                      Ins_Player ('São Paulo', 'Juan', 'WB', 7.89, 4.57, 3);  
                                      Ins_Player ('São Paulo', 'Paulo Miranda', 'WB', 10.53, 4.54, 5);  
                                      Ins_Player ('Flamengo', 'João Paulo', 'WB', 7.15, 4.53, 3);  
                                      Ins_Player ('São Paulo', 'Rodrigo Caio', 'WB', 11.92, 4.52, 5);  
                                      Ins_Player ('Coritiba', 'Victor Ferraz', 'WB', 13.04, 4.2, 5);  
                                      Ins_Player ('Bahia', 'Jussandro', 'WB', 6.94, 4.1, 5);  
                                      Ins_Player ('Santos', 'Rafael Galhardo', 'WB', 12.88, 4.04, 5);  
                                      Ins_Player ('Goiás', 'William Matheus', 'WB', 5.87, 4.02, 5);  
                                      Ins_Player ('Náutico', 'Maranhão', 'WB', 6.53, 4.02, 5);  
                                      Ins_Player ('Internacional', 'Gabriel', 'WB', 11.81, 3.38, 5);  
                                      Ins_Player ('Goiás', 'Vítor', 'WB', 8.77, 3.36, 5);  
                                      Ins_Player ('Internacional', 'Fred', 'MF', 30.28, 8.92, 5);  
                                      Ins_Player ('Grêmio', 'Zé Roberto', 'MF', 25.93, 8.78, 4);  
                                      Ins_Player ('Internacional', 'Otavinho', 'MF', 7.62, 8.07, 3);  
                                      Ins_Player ('Vasco', 'Carlos Alberto', 'MF', 15.01, 6.75, 2);  
                                      Ins_Player ('Cruzeiro', 'Nilton', 'MF', 22.39, 6.46, 5);  
                                      Ins_Player ('Coritiba', 'Júnior Urso', 'MF', 14.38, 6.22, 5);  
                                      Ins_Player ('Crisciúma', 'João Vitor', 'MF', 13.27, 6.04, 5);  
                                      Ins_Player ('Corinthians', 'Guilherme', 'MF', 8.83, 5.87, 4);  
                                      Ins_Player ('Corinthians', 'Ralf', 'MF', 19.65, 5.7, 5);  
                                      Ins_Player ('Vitória', 'Escudero', 'MF', 16.38, 5.68, 5);  
                                      Ins_Player ('Portuguesa', 'Correa', 'MF', 8.44, 5.6, 4);  
                                      Ins_Player ('Portuguesa', 'Souza', 'MF', 12.62, 5.17, 4);  
                                      Ins_Player ('Coritiba', 'Alex', 'MF', 16.98, 5.08, 5);  
                                      Ins_Player ('Grêmio', 'Souza', 'MF', 13.8, 4.98, 4);  
                                      Ins_Player ('Ponte Preta', 'Cicinho', 'MF', 11.42, 4.72, 5);  
                                      Ins_Player ('Botafogo', 'Fellype Gabriel', 'MF', 8.6, 4.47, 4);  
                                      Ins_Player ('Atlético-PR', 'João Paulo', 'MF', 10.56, 4.38, 5);  
                                      Ins_Player ('Vasco', 'Sandro Silva', 'MF', 10.76, 4.28, 5);  
                                      Ins_Player ('Santos', 'Cícero', 'MF', 14.15, 4.18, 5);  
                                      Ins_Player ('Fluminense', 'Wagner', 'MF', 8.55, 4.13, 3);  
                                      Ins_Player ('Flamengo', 'Jaime De AlMFda', 'CO', 11.56, 8.03, 1);  
                                      Ins_Player ('Cruzeiro', 'Marcelo Oliveira', 'CO', 16.11, 5.43, 5);  
                                      Ins_Player ('Fluminense', 'Abel Braga', 'CO', 17.51, 5.36, 4);  
                                      Ins_Player ('Internacional', 'Dunga', 'CO', 14.22, 4.63, 5);  
                                      Ins_Player ('Vitória', 'Caio Júnior', 'CO', 11.4, 4.45, 5);  
                                      Ins_Player ('Grêmio', 'Vanderlei Luxemburgo', 'CO', 15.77, 4.42, 4);  
                                      Ins_Player ('São Paulo', 'Ney Franco', 'CO', 15.15, 4.39, 5);  
                                      Ins_Player ('Náutico', 'Levi Gomes', 'CO', 7.08, 4.2, 2);  
                                      Ins_Player ('Atlético-PR', 'Ricardo Drubscky', 'CO', 7.96, 3.92, 5);  
                                      Ins_Player ('Coritiba', 'Marquinhos Santos', 'CO', 10.59, 3.89, 5);  
                                      Ins_Player ('Vasco', 'Paulo Autuori', 'CO', 13.13, 3.61, 5);  
                                      Ins_Player ('Portuguesa', 'Edson Pimenta', 'CO', 3.67, 3.26, 4);  
                                      Ins_Player ('Botafogo', 'Oswaldo De Oliveira', 'CO', 10.77, 3.23, 5);  
                                      Ins_Player ('Corinthians', 'Tite', 'CO', 13.68, 3.17, 5);  
                                      Ins_Player ('Santos', 'Claudinei Oliveira', 'CO', 11.92, 3.17, 3);  
                                      Ins_Player ('Bahia', 'Cristóvão Borges', 'CO', 8.27, 2.92, 5);  
                                      Ins_Player ('Crisciúma', 'Vadão', 'CO', 7.04, 2.86, 5);  
                                      Ins_Player ('Goiás', 'Enderson Moreira', 'CO', 6.8, 2.53, 5);  
                                      Ins_Player ('Atlético-MG', 'Cuca', 'CO', 12.62, 2.32, 4);  
                                      Ins_Player ('Ponte Preta', 'Zé Sérgio', 'CO', 6.85, .75, 1);  
                                      Ins_Player ('Fluminense', 'Digão', 'CB', 9.31, 9.27, 3);  
                                      Ins_Player ('Flamengo', 'Samir', 'CB', 2.67, 6.8, 1);  
                                      Ins_Player ('Cruzeiro', 'Dedé', 'CB', 22.54, 6.4, 5);  
                                      Ins_Player ('São Paulo', 'Lúcio', 'CB', 21.71, 6.02, 5);  
                                      Ins_Player ('Grêmio', 'Bressan', 'CB', 10.85, 5.9, 4);  
                                      Ins_Player ('Atlético-PR', 'Manoel', 'CB', 16.99, 5.88, 5);  
                                      Ins_Player ('Ponte Preta', 'Cléber', 'CB', 14.61, 5.78, 5);  
                                      Ins_Player ('Cruzeiro', 'Bruno Rodrigo', 'CB', 15.47, 5.28, 5);  
                                      Ins_Player ('Santos', 'Edu Dracena', 'CB', 16.82, 4.97, 3);  
                                      Ins_Player ('Náutico', 'William Alves', 'CB', 5.56, 4.43, 3);  
                                      Ins_Player ('Fluminense', 'Gum', 'CB', 12.18, 4.22, 4);  
                                      Ins_Player ('Flamengo', 'Wallace', 'CB', 4.29, 4.2, 2);  
                                      Ins_Player ('Náutico', 'João Filipe', 'CB', 5.47, 4.1, 4);  
                                      Ins_Player ('Grêmio', 'Werley', 'CB', 15.9, 4.03, 4);  
                                      Ins_Player ('Corinthians', 'Gil', 'CB', 13.23, 3.98, 5);  
                                      Ins_Player ('Vitória', 'Gabriel Paulista', 'CB', 11.77, 3.94, 5);  
                                      Ins_Player ('Goiás', 'Ernando', 'CB', 10.24, 3.74, 5);
                                    --  DELETE players WHERE player_name > 'Cristóvão Borges';--'Gil';-- 'Cássio';
                                    
                                    /*  Ins_Player ('Grêmio',            'Bressan',          'CB',     '1085',    '590',     '4');
                                      Ins_Player ('Cruzeiro',            'Bruno Rodrigo',     'CB',     '1547',    '528',     '5');
                                      Ins_Player ('Ponte Preta',     'Cléber',          'CB',     '1461',    '578',     '5');
                                    
                                      Ins_Player ('Náutico',            'Auremir',          'WB',     '773',     '548',     '4');
                                      Ins_Player ('Fluminense',            'Carlinhos',          'WB',     '1240',    '693',     '3');
                                    
                                      Ins_Player ('Coritiba',            'Alex',          'MF',     '1698',    '508',     '5');
                                      Ins_Player ('Vasco',            'Carlos Alberto',     'MF',     '1501',    '675',     '2');
                                      Ins_Player ('Ponte Preta',     'Cicinho',          'MF',     '1142',    '472',     '5');
                                      Ins_Player ('Santos',            'Claudinei Oliveira','CO',     '1192',    '317',     '3');
                                      Ins_Player ('Portuguesa',            'Correa',          'MF',     '844',     '560',     '4');
                                    
                                      Ins_Player ('Ponte Preta',     'Chiquinho',          'FW',     '997',     '464',     '5');
                                      Ins_Player ('Grêmio',            'Barcos',          'FW',     '1896',    '367',     '4');
                                    
                                      Ins_Player ('Bahia',            'Cristóvão Borges',     'CO',     '827',     '292',     '5');
                                      Ins_Player ('Atlético-MG',     'Cuca',          'CO',     '1262',    '232',     '4');
                                      Ins_Player ('Vitória',            'Caio Júnior',     'CO',     '1140',    '445',     '5');
                                      Ins_Player ('Fluminense',            'Abel Braga',          'CO',     '1751',    '536',     '4');
                                    
                                      Ins_Player ('Crisciúma',            'Bruno',          'GK',     '1066',    '320',     '5');
                                      Ins_Player ('Corinthians',     'Cássio',          'GK',     '1251',    '374',     '5');
                                    */
                                    END;
                                    /
                                    
                                    PROMPT Input Formations
                                    SELECT *
                                      FROM formations
                                     ORDER BY 1
                                    /
                                    COLUMN club_name FORMAT A15
                                    COLUMN player_name FORMAT A20
                                    PROMPT Input Players
                                    SELECT id,
                                           club_name,
                                           player_name,
                                           position,
                                           price,
                                           avg_points,
                                           appearances,
                                           avg_points * appearances tot_points,
                                           Round (avg_points * appearances / price, 2) utility,
                                           Rank() OVER (ORDER BY avg_points * appearances DESC) t_rank,
                                           Rank() OVER (ORDER BY price) p_rank
                                      FROM players
                                     ORDER BY avg_points * appearances / price DESC
                                    /
                                    SET TIMING ON
                                    COLUMN path FORMAT A30
                                    COLUMN node FORMAT A10
                                    COLUMN p_id FORMAT A4
                                    BREAK ON tot_profit ON tot_price ON rnk ON position
                                    VAR KEEP_NUM NUMBER
                                    BEGIN
                                      :KEEP_NUM := 100;
                                    END;
                                    /
                                    PROMPT Top ten solutions
                                    WITH players_ranked AS (
                                    SELECT id,
                                           position,
                                           price,
                                           avg_points,
                                           appearances, 
                                           Row_Number() OVER (ORDER BY avg_points DESC) rnk
                                      FROM players
                                    ), rsf (dt, nxt_id, lev, tot_price, tot_profit, n_cb, n_wb, n_mf, n_fw, path) AS (
                                    SELECT Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC), 
                                           0.0001 * Row_Number() OVER (ORDER BY (p1.avg_points + p2.avg_points) DESC), 
                                           0, p1.price + p2.price, p1.avg_points + p2.avg_points, 
                                           0, 0, 0, 0,
                                           CAST (LPad (p1.id, 3, '0') || LPad (p2.id, 3, '0') AS VARCHAR2(4000)) path
                                      FROM players p1
                                      JOIN players p2
                                        ON p1.position = 'CO'
                                       AND p2.position = 'GK'
                                     UNION ALL
                                    SELECT Row_Number() OVER (ORDER BY r.tot_profit + p.avg_points DESC),-- * r.dt, 
                                           p.rnk, 
                                           r.lev + 1, 
                                           r.tot_price + p.price,
                                           r.tot_profit + p.avg_points,
                                           r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END,
                                           r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END,
                                           r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END,
                                           r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END,
                                           r.path || LPad (p.id, 3, '0')
                                      FROM rsf r
                                      JOIN players_ranked p
                                        ON p.rnk > r.nxt_id
                                     WHERE EXISTS (SELECT 1 FROM formations WHERE 
                                                       centre_backs >= r.n_cb + CASE p.position WHEN 'CB' THEN 1 ELSE 0 END AND
                                                       wing_backs   >= r.n_wb + CASE p.position WHEN 'WB' THEN 1 ELSE 0 END AND
                                                       midfielders  >= r.n_mf + CASE p.position WHEN 'MF' THEN 1 ELSE 0 END AND
                                                       forwards     >= r.n_fw + CASE p.position WHEN 'FW' THEN 1 ELSE 0 END
                                                  )
                                      AND p.position NOT IN ('CO', 'GK')
                                      AND r.tot_price + p.price